ALL EXCEPT Filter no functioning as expected

[Anonymous]

Hi,

I have two slicers. The first displays a unique medical practice identifier. The second displays the date. I want two measures. The first should calculate the number of patients seen by the practice during the selected time period. And the second should calculate the number of patients for all practices excluding the practice that has been selected. 

For example I have 5 practices that have submitted 2000 claims for 100 patients over 2 months. However in 1 month there were 3 practices that subitted 750 claims for 55 patients. I select one of those 3 practices i calculate that this practice submitted 150 claims for 15 patients then remaining 2 practices submitted 600 claims from 40 patients.

I have created the following measures:

No of Patients(Selected) = DISTINCTCOUNT('Claims Fact'[Patient ID])

No of Patients(Not Selected) = CALCULATE(DISTINCTCOUNT('Claims Fact'[Patient ID]); ALLEXCEPT('Claims Fact';'Claims Fact'[Date ID])) - [No of Patients(Selected)]

This first works, the second doesn't appear to be working when I change the Date slicer from the full range to say a single month.

My understanding of the above DAX expression is the following:

1. Take the Claims Fact table and remove all filter contexts except the Date filter context (this should remove the filter context applied by the 1st practice slicer but keep any filter contect by the date slicer)

2. Calculate the distinct count of the number of patients using the result produced above

3. Subtract  the no of patients from the selected practice to get the balance of patients from the other practices

So using the example above:

Slicer 1 = Practice A

Slicer 2 = 1/01/2016-31/01/2016 (The full range runs to the end of Feb 2016)

Measure 1 = 15

Steps for measure 2

1. Remove all filter context  except the date. 

2. Count the number of patients including patients from Practice A (As this filter context has been removed). This should be 55

3. Take the result from step2 and subtract the result from the first measure. This should be 40 (55-15)

Any help appreciated.

Thanks,

Paul

[v-ljerr-msft]

Hi @Anonymous,

Your understanding is right. Based on my test, the formulas should work in your scenario.

What's the currently result? Could you post your table structures with some sample/mock data which can help us reproduce this issue, so that we can help further investigate on it? 

It's better to share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Anonymous · Accepted solution]

@v-ljerr-msft Thanks for the response. I managed to get it working by removing the filter context on the Practice Dimension. Here is th updated measure:

Measure = CALCULATE(DISTINCTCOUNT('Claims Fact'[Patient ID]); ALL('Practice Dimension'))

Regards,

Paul