topic Re: Count rows in lookup table based on specific values missing in fact table in DAX Commands and Tips

Count rows in lookup table based on specific values missing in fact table

GavinH — Wed, 01 Jul 2020 20:07:45 GMT

(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

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.

Re: Count rows in lookup table based on specific values missing in fact table

mahoneypat — Sat, 04 Jul 2020 23:12:26 GMT

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

Re: Count rows in lookup table based on specific values missing in fact table

AntrikshSharma — Sun, 05 Jul 2020 14:20:19 GMT

Great solution Pat, Gavin Just add 0 after the last bracket and you should get the exact result as you wanted.

Re: Count rows in lookup table based on specific values missing in fact table

GavinH — Mon, 06 Jul 2020 11:24:07 GMT

Thanks so much, mahoneypat ! That seems to work great.

Thanks!