topic Dynamic DAX Measure to Count Distinct Rows filtered by separate query in DAX Commands and Tips

Dynamic DAX Measure to Count Distinct Rows filtered by separate query

jjfr — Mon, 05 Aug 2024 07:56:00 GMT

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.

Re: Dynamic DAX Measure to Count Distinct Rows filtered by separate query

Dangar332 — Mon, 05 Aug 2024 08:07:07 GMT

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.

Re: Dynamic DAX Measure to Count Distinct Rows filtered by separate query

jjfr — Mon, 05 Aug 2024 08:29:48 GMT

Brilliant thanks so much for that Dangar332! Works perfectly