Weird behavior when mixing USERELATIONSHIP and CALCULATE

[rickard_9]

Hello,

My worksheet has these 2 tables: (Title is blank whenever Special Title is not)

The relationship to Assigned City Key is inactive:

These are my measures:

Current non-accountants:=CALCULATE(COUNTROWS(Employees);
Employees[Title] <> "Accountant";
Employees[Title] <> BLANK()
)

Assigned non-accountants:=CALCULATE([Current non-accountants]; USERELATIONSHIP(Employees[Assigned City Key]; Cities[City Key]))

And the resulting pivot:

If, however, I change the "Current non-accountants" measure to use FILTER instead of boolean arguments to CALCULATE, like this...

Current non-accountants:=CALCULATE(COUNTROWS(Employees);
FILTER(Employees;
Employees[Title] <> "Accountant" &&
Employees[Title] <> BLANK()
)
)

... then I get wrong results for "Assigned non-accountants" in the pivot:

I'm aware that the FILTER version will keep the current filter context for the Title column, unlike the FILTER-less version. But that should not be an issue since there's no filter context on Title to begin with.

If I change the active relationship, then Assigned non-accountants will give the correct results for both versions (and Current non-accountants, of course, will be equal to Assigned non-accountants).

Can someone please clarify this behavior?

 

[Matt Allington]

I only know 1 Rickard that knows this much DAX and cares about accountants - is that you Derek? Anyway, I believe this is not a USERELATIONSHIP problem but a logical AND problem. The double ampersand version is structurally flawed, as every row must pass the the test.

It's like the riddle (converted for USA). "I have 2 coins that total 15 cents, and one of them is not a nickel. What are the 2 coins?" The answer is a nickel and a dime. The dime is not a nickel, so it passes the logical test.

In your case if the rows is an Accountant, it passes the not BLANK() test. If it is BLANK then it passes the not Accountant test.

Try ray swapping it to a logical OR with double pipes.

 

[Matt Allington]

Oh, and the reason the first one works is because there are 2 separate logical conditions that are both applied. The second one is 1 single condition with 2 choices.

 

[rickard_9]

LOL, no, I'm not him.

Thank you very much for your help. Well, I tried what you suggested, but then Current non-accountants becomes broken too. My measures should actually be named "non-accountants and non-special titles". I really want to exclude the blanks because those correspond to special titles. But anyway, isn't CALCULATE supposed to combine its boolean arguments using a logical AND?

I also tried rewriting the FILTER version to the exact equivalent of the FILTER-less version (using ALL to also overwrite the filter context like the FILTER-less version):

Current non-accountants:=CALCULATE(COUNTROWS(Employees);
FILTER(ALL(Employees[Title]);
Employees[Title] <> "Accountant" &&
Employees[Title] <> BLANK()
)
)

With this version, both measures work.

So how can overwriting a filter context which doesn't even exist make any difference when the second measure puts USERELATIONSHIP into the mix? That's what's puzzling me.

 

[Matt Allington]

Ah yes, my bad. Wrong diagnosis - sorry.

I believe the issue maybe the fact that you have nested measures. The internal measure is using the active relationship and the external calculate is using the inactive one. So my guess is if you replaced the internal measure with the raw formula inside the first measure, then it will probably work.

Let me know.

 

[rickard_9]

Thank you again.

Well, things got even weirder.

If I simply substitute the measure reference for its formula so as to get an exact equivalent, I get the same wrong pivot from my first post:

Assigned non-accountants:=CALCULATE(CALCULATE(COUNTROWS(Employees);
FILTER(Employees;
Employees[Title] <> "Accountant" &&
Employees[Title] <> BLANK()
));
USERELATIONSHIP(Employees[Assigned City Key]; Cities[City Key])
)



If I drop the inner CALCULATE...


Assigned non-accountants:=CALCULATE(COUNTROWS(Employees);
FILTER(Employees;
Employees[Title] <> "Accountant" &&
Employees[Title] <> BLANK()
);
USERELATIONSHIP(Employees[Assigned City Key]; Cities[City Key])
)

...then Current non-accountants is still correct and Assigned non-accountants is still wrong but with different values!

With these values, it seems that this is what's happening:

1) For Houston, for example, original filter context is just Cities[City Name]=Houston (there's no filter context for Title).

2) FILTER will be applied using the active relationship yielding this table:

3) At this point, Current non-accountants would correctly return 6. But Assigned non-accountants will ONLY THEN change the active relationship and then re-apply the FILTER over the table in step 2! Only the Paul line will remain and Assigned non-accountants returns 1.

What seems to confirm this is the fact that, if I change the FILTER in this last formula to use ALL(Employees[Title]), it works! Apparently, because the FILTER applied in step 2 will be cleared before re-applying the FILTER with the changed relationship in step 3. So this second filter will apply over the entire table, yielding the correct result of 2 (Paul and Gary).

This is crazy. I thought USERELATIONSHIP changed the active relationship BEFORE applying any filters in the CALCULATE.

I still can't find an explanation for the different wrong values I got initially (with the measure reference or the nested CALCULATE).

 

[rickard_9]

I solved the issue. I still don't understand exactly the reason, but the filter context on Cities[City Name] coming from the pivot won't propagate correctly to the inner CALCULATE unless we explicitly place it there:

Current non-accountants:=CALCULATE(COUNTROWS(Employees);
FILTER(Employees;
Employees[Title] <> "Accountant" &&
Employees[Title] <> BLANK()
);
Cities
)

Assigned non-accountants:=CALCULATE([Current non-accountants]; USERELATIONSHIP(Employees[Assigned City Key]; Cities[City Key]))

Now I can even do this: