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...
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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
6 years ago
Great solution Pat, Gavin Just add 0 after the last bracket and you should get the exact result as you wanted.