Distinct Count with ALL function to ignore slicer not working

[AgeOfEgos]

I have a slicer that has a drop down of possible services a patient will receive.  I have a measure that counts ALL patients regardless of service, then another measure that counts only the selected service distinct patients.  The idea is, I can then produce a % of patients that receive that service.  

While my service disctinct count measure works, the measure doing a distinct count ignoring the service slicer does not.  Here is my code:

ALLPATIENTS = CALCULATE(DISTINCTCOUNT(Sheet1[Name]),ALL(Sheet1[Service]))

However, if I select a service from the slider--the card that has the above measure attached will change to reflect that selection.  

Thanks for any help!

[NAOS]

Hi,

Easiest way to solve it is to create a calendar table and use that as your report slicer. You will also have to create tables for other attributes when necessary. DAX is powerful but needs a good data model in behind to work in a more predictable way.

And use this measure. 

ALLPATIENTS = CALCULATE(DISTINCTCOUNT(Sheet1[Name]),ALL(Sheet1))

That should do.

Kind regards,

[AgeOfEgos]

The file

https://drive.google.com/file/d/1Lu4EK1lwOpXZKjwpLO3SsDZPFqxIXolG/view?usp=sharing

[v-chuncz-msft]

@AgeOfEgos,

There is another slicer. Just use ALL ( Sheet1 ).

[AgeOfEgos]

That did work as a test--but I need the date slicer to still effect that total.  Just not the service slicer

[v-chuncz-msft · Accepted solution]

@AgeOfEgos,

Take a look at Change how visuals interact in a report and select the no impact icon.

[AgeOfEgos]

and yeah I've tried ALLEXCEPT(Sheet1, Sheet1[Date]) but the moment I put an ALL or ALLEXCEPT back on that measure--it starts using the Service slicer again.

[v-chuncz-msft]

@AgeOfEgos,

If the field dragged to slicer comes from another table that has relationship to Sheet1, you may change it to ALL ( Table2[Service] ).

[AgeOfEgos]

Only one table in the file

[MFelix]

Hi @AgeOfEgos,

I don't see any issues with the measure you have and should work as expected can you give some more insight on the model.

Is the slicer you are using from the same table?

Are you placing the correct meausre on the card you are visualizing?

Can you share some sample file.

Regards,

MFelix