DISTINCTCOUNT with condition

[vickyw]

Hi All,

I am surprised this question hasn't come up more - I found a similar post but it didn't the solution didn't work for my situation.

So, I have a PBI report stuffed with measures that look like this:

Total Red Cases = CALCULATE(DISTINCTCOUNT('All Cases'[Case Number]), 'All Cases'[CaseColour] = "Red")

The measures always perform a distinct count on the [Case Number] field, where a particular criteria is true i.e., where the [CaseColour] field is 'Red'

But I have just discovered that the CALCULATE function overrides the page slicers, so when the end user filters out all the red cases, I would expect the value in my measure 'Total Red Cases' to go to zero however, it remains unchanged.

Is there anyway to prevent CALCULATE from overriding the page filters? Or is there a different way to write the DAX to get around this? Any help appreciated

[vickyw]

It absolutely does! Thank you so much!! 😄

[rsbin · Accepted solution]

@vickyw ,

I believe the KeepFilters function will help in this situation:

Total Red Cases = CALCULATE(DISTINCTCOUNT('All Cases'[Case Number]), 
                        KEEPFILTERS('All Cases'[CaseColour] = "Red"))

https://learn.microsoft.com/en-us/dax/keepfilters-function-dax
for a completed explanation.

Hope this is what you are aiming for.

Regards,