Forum Discussion
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 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.
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,
DangarIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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,
DangarIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
