Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
GavinH
Frequent Visitor

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.

1 ACCEPTED SOLUTION
mahoneypat
Employee
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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Employee
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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 

Thanks!

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors