Remove filter not removing a drop-down slicer

[gbaia]

hello!

I tried to solve this, but I'm not managing and I'd love some help.

I connect to data straight via SQL Server, so it's live data. The schema is not ideal so I change it slightly to make it work once it loads.

In summary, my issue is with the following tables:

- cases table: case ID, client ID, project ID, open date, closed date
- clients table: client ID, gender
- projects table: project ID, project name
- probject_client table: project ID, client ID

As shown above, cases and clients are 1:M (1 client can have many cases, 1 case has 1 client); projects and cases are 1:1; clients and projects are M:M.

Then I have a table where I have the breakdown (gender, distinct client ID, distinct client and project IDs). Both counts are measures, they count the distinct client IDs within the date range; and the distinct client and project IDs within the date range.

As well as the date slicer, I have a project name drop-down / slicer.

The problem is that I want both those columns to ignore the project name slicer. I tried ALL,REMOVEFILTERS, but it never works. I think the relationships are too complicated or something.

Any ideas?

Thank you! 

Measure that counts the clients:
CountClient inc blank = CALCULATE(DISTINCTCOUNT('cases'[Client_ID]),union(filter(all(CloseDateTable),CloseDateTable[Date]=blank()),CloseDateTable))

I add:
ALL('cases'),ALL('clients') OR
REMOVEFILTER and none work

Not filtered and ideally I'd keep this unfiltered by project name, but have exactly the same count next to it, which would be filtered by project. 

[gbaia]

Thank you @v-jianpeng-msft - I'm trying this now. After lots of time trying to debug it, I noticed that the count which is mean to be unfiltered by project, removes all cases where the closed date is not blank. I'll be trying your file / solution now as well. 

I did a table to debug. The DAX below is the closest I can get the results, but weirdly, it removes the filters where the closed date is not blank unless it's the project selected.

So I selected on my slice project ID 151, I get all the count for the project ID 151 PLUS all the count for the cases where the closed date is blank. That's why the number is higher than the selected, but still not what it should be without any filter.

I try to give an example below. Thank you so much!!

Without filter:
Gender - Count1 - Count2 (remove filter)
Not say - 4 - 4

With filter project ID = 151
Gender - Count1 - Count2 (remove filter)
Not say - 0 - 2 (it should be 4)

I checked the columns with higher numbers (eg: female) and it's the same issue. No idea why. Adding the allselected, brings lots of unfiltered records even with genders that aren't on the original table.

Count1 formula:
CountClient inc blank = CALCULATE(DISTINCTCOUNT('cases'[Client_ID]),union(filter(all(CloseDateTable),CloseDateTable[Date]=blank()),CloseDateTable))

Count2 formula - the ones that shouldn't be filtered, but it's getting filtered by the date closed not blank:
CountClient inc blank2 =
CALCULATE(
DISTINCTCOUNT('cases'[Client_ID]),
UNION(
FILTER(ALL(CloseDateTable), CloseDateTable[Date] = BLANK()),
CloseDateTable
),
REMOVEFILTERS ('projects'),
REMOVEFILTERS ('cases'[Project_ID] ),
REMOVEFILTERS('projects_clients'[Project_ID]))

[gbaia]

Thank you so much, I'm trying this now and will get back to you. Thanks 🙂

[v-jianpeng-msft]

Hi, @gbaia 

The issue you mentioned is most likely due to an issue with your relationship setup that is causing the incorrect result. I'll use the following model to build a report similar to yours:

I create a measure using the following DAX expression:

Measure = CALCULATE(DISTINCTCOUNT('Cases Table'[Client_ID]),ALL('Projects Table'[Project_Name]))

Create a table visual with these fields:

The corresponding slicer is created using the following two fields:

When I switch projects, my measure results are not affected:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.