Forum Discussion

jjfr's avatar
jjfr
Frequent Visitor
2 years ago
Solved

Dynamic DAX Measure to Count Distinct Rows filtered by separate query

Hi All, I'm pretty new to DAX and struggling to figure this out so any help is greatly appreciated. I have the following data model (simplified):     I am trying to write a measure tha...
DAX
Distinct
Filter
measure
multiple tables
  • Dangar332's avatar
    Dangar332
    2 years ago

    Hi, jjfr You use ALL('Appointments') it kill effect of outer filter from same table(Appointments) and related expanded table.

    Try below measure

     

    # Appointment Attendees (Distinct Students) = 
CALCULATE ( DISTINCTCOUNT('Enrolment'[Student ID]), 
FILTER(ALL('Appointments'[Attendance Status]), 'Appointments'[Attendance Status] = 2)

     

     

    Best regards,
    Dangar 

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

Hi, jjfr You use ALL('Appointments') it kill effect of outer filter from same table(Appointments) and related expanded table.

Try below measure

 

# Appointment Attendees (Distinct Students) = 
CALCULATE ( DISTINCTCOUNT('Enrolment'[Student ID]), 
FILTER(ALL('Appointments'[Attendance Status]), 'Appointments'[Attendance Status] = 2)

 

 

Best regards,
Dangar 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

jjfr's avatar
jjfr
Frequent Visitor
2 years ago

Brilliant thanks so much for that Dangar332! Works perfectly