Forum Discussion

GavinH's avatar
GavinH
Frequent Visitor
6 years ago
Solved

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:

RegistrationIDRegistrationYearTotalNoOfSessions
143_END20125
148_NOS20125
145_ACH20136
150_CCC20136

 

And this is my fact table: 

Attendance Table:

RegistrationIDSessionNumberAttended
143_END1
143_END2
143_END3
143_END4
143_END5
148_NOS1
148_NOS3
148_NOS4
148_NOS5
145_ACH1
145_ACH2
145_ACH5
150_CCC1
150_CCC2
150_CCC3
150_CCC4
150_CCC5
150_CCC6

 

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 Year123456
2012010002
2013001101

 

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

  • mahoneypat's avatar
    mahoneypat
    Microsoft 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

    • AntrikshSharma's avatar
      AntrikshSharma
      Community Champion
      Great solution Pat, Gavin Just add 0 after the last bracket and you should get the exact result as you wanted.