Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
gbaia
New Member

Remove filter not removing a drop-down slicer

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_0-1727687053081.png

 

3 REPLIES 3
gbaia
New Member

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
New Member

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

v-jianpeng-msft
Community Support
Community Support

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:

vjianpengmsft_0-1727764094416.png

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:

vjianpengmsft_1-1727764185388.png

The corresponding slicer is created using the following two fields:

vjianpengmsft_2-1727764323163.png

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

vjianpengmsft_3-1727764411548.png

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.