Forum Discussion
Count rows in lookup table based on specific values missing in fact table
(Updated this as my sample matrix was incorrect)
Hi,
I am still learning DAX and hope someone can help with this. I am looking for a way to create a measure that counts the number of entries in a lookup table that do NOT have a corresponding entry in a related fact table that matches specific criteria (based on a third, unrelated reference/slicer table).
As a much simplified illustration, let's say this is my lookup table:
Registration Table:
| RegistrationID | RegistrationYear | TotalNoOfSessions |
| 143_END | 2012 | 5 |
| 148_NOS | 2012 | 5 |
| 145_ACH | 2013 | 6 |
| 150_CCC | 2013 | 6 |
And this is my fact table:
Attendance Table:
| RegistrationID | SessionNumberAttended |
| 143_END | 1 |
| 143_END | 2 |
| 143_END | 3 |
| 143_END | 4 |
| 143_END | 5 |
| 148_NOS | 1 |
| 148_NOS | 3 |
| 148_NOS | 4 |
| 148_NOS | 5 |
| 145_ACH | 1 |
| 145_ACH | 2 |
| 145_ACH | 5 |
| 150_CCC | 1 |
| 150_CCC | 2 |
| 150_CCC | 3 |
| 150_CCC | 4 |
| 150_CCC | 5 |
| 150_CCC | 6 |
My third unrelated table will be used as matrix column headers for the sessions:
Sessions Table:
| SessionNumber |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
In my matrix, I would like to get a result something like this (Note: Column headers are drawn from the Slicer table.):
| Registration Year | 1 | 2 | 3 | 4 | 5 | 6 |
| 2012 | 0 | 1 | 0 | 0 | 0 | 2 |
| 2013 | 0 | 0 | 1 | 1 | 0 | 1 |
Normally, I would simply subtract the number of attendees for each session from the total registrations. However, in this case, I will later need to apply specific criteria to the registrations which are missing records (e.g. to differentiate between isolated absences and full withdrawals), so I need to somehow POSITIVELY identify the missing records by session and registration. Is there a measure that could be used to identify these "missing" records?
Thanks.
You should be able to use a measure like this to get your desired result in your matrix visual.
Missing Registrations = COUNTROWS ( FILTER ( VALUES ( Registration[RegistrationID] ), ISBLANK ( CALCULATE ( COUNTROWS ( Attendance ), TREATAS ( VALUES ( Sessions[Session Number] ), Attendance[SessionNumberAttended] ) ) ) ) )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
3 Replies
- mahoneypatMicrosoft Employee
You should be able to use a measure like this to get your desired result in your matrix visual.
Missing Registrations = COUNTROWS ( FILTER ( VALUES ( Registration[RegistrationID] ), ISBLANK ( CALCULATE ( COUNTROWS ( Attendance ), TREATAS ( VALUES ( Sessions[Session Number] ), Attendance[SessionNumberAttended] ) ) ) ) )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- AntrikshSharmaCommunity ChampionGreat solution Pat, Gavin Just add 0 after the last bracket and you should get the exact result as you wanted.
- GavinHFrequent Visitor