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

[GavinH]

(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.

[mahoneypat · Accepted solution]

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

[GavinH]

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

Thanks!

[AntrikshSharma]

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