Dynamic DAX Measure to Count Distinct Rows filtered by separate query

[jjfr]

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 that runs a distinct count on the number of students ('Enrolment'[Student ID]) that have attended an appointment ('Appointments'[Attendance Status]=2). I then want to be able to slice on this measure by date, dimensions etc.

The following is currently giving me the correct total over all my data:

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

However I have a number of other slicers in my report, drawing on values from fact and dim tables. These slicers are not working with the measure in the report , it only displays the original value.

Is there a way to re-write the measure so it still works dynamically with slicers?

Thanks in advance for your help.

[Dangar332 · Accepted solution]

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]

Brilliant thanks so much for that @Dangar332! Works perfectly