DAX - Countrows Filter where rows not exist

[tinker]

have 2 dimension tables joined to CardUser fact table which stores Clock in/out records of each user.

The following measure counts the dates where CardUser also contains a row with the same dates,

CALCULATE (
        COUNTROWS(Dates)
        ,
        FILTER (
            CardUser,
            CardUser[CheckIn_DateFormat] <> BLANK()
        )
)

Is there a way to filter for rows where CardUser "DOES NOT" contain matching rows? The only way I can think of is building a table with Dates antijoin CardUser on the date column. Is there a simpler way?

Example test case,

User table = (userA,userB)

Date table = (20190401,20190402,20190403)

CardUser table = ((20190401,userA),(20190402,userA))

The formula should give 3 for userB. All 3 dates do not have corresponding row in CardUser table for UserB.

The formula should give 1 for userA. Only 1 date does not have a corresponding row.

[Zubair_Muhammad · Accepted solution]

@tinker 

Please see attached file with test data as well

[tinker]

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.

Link to download updated PBIX

[Zubair_Muhammad]

@tinker 

Try this MEASURE

Measure 2 =
COUNTROWS (
    EXCEPT (
        VALUES ( Dates[Date] ),
        CALCULATETABLE (
            VALUES ( Dates ),
            CROSSFILTER ( CardUser[CheckIn_DateFormat], Dates[Date], BOTH )
        )
    )
)