Forum Discussion
DAX - Countrows Filter where rows not exist
Wow interesting, can u explain how this works?
I'm having trouble understanding why CROSSFILTER is required. Isn't the relationship in the model enough?
Also it seems that the arguments for except function is (dates,dates). Both are referring to the same dates table? How does this get what we want?
Any drawbacks of using bi-directional filters by default in the model. So we don't have to use crossfilter?
---
Answering my own questions, breaking down the formula bit by bit really helps in understanding. I also put the dates inside the table so you can see exactly what dates are being returned rather than the aggregated value.
VALUES(Dates) - returns a list of distinct values on the dates. It will give all 3 dates in the table, regardless of userA / userB. Why? Because we cannot go from CardUser table to the Dates table in the data model to display ONLY the dates that are associated with each user. So effectively Dates table is on it's own, no filters.
CALCULATETABLE - evaluates a table/list based on a filter, which is specified in the CROSSFILTER line.
CROSSFILTER - specifies the relationship that we want to change the direction. We are changing the link between Dates and CardUser from default one direction to bi-directional.
Now, the user table is able to properly use the relationship to lookup which dates are related. And only show those.
EXCEPT(A,B) - Return rows in A, after removing similar rows from B.
EXCEPT(Dates - unfiltered, Dates - filtered)
= EXCEPT(3, Dates - filtered by user)
This is evaluated in context. Meaning for each user separately, in the table above.
eg. for userA,
EXCEPT(3 dates total in date table , 2 dates associated for userA) = Return the 1 date that is not associated.
Interestingly, it works same if we use DISTINCT as well, which is same as VALUES except that it will take into account data that is not joined properly. Because we are only using data that has been properly joined properly in both tables for second date table(Calculatetable ...), we won't see any difference between DISTINCT and VALUES.