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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DawdF95Rze
Frequent Visitor

Remove filters doesnt work with slicer's filter

Hi

 

I have the same problem as Remy Bosman in this post (Solved: Removefilters not working - Microsoft Power BI Community), but the solution that @wdx223_Daniel  passed doesn't work with slicer's filter 😞 Can you explain why it doesn't work?

 

Regards

7 REPLIES 7
ali_b
Advocate I
Advocate I

Hi, I'm having a really basic issue with "removefilters" not working in conjunction with slicers and this seems like the most appropriate thread.

See the attached pbix file that shows two problems. Oh wait. This forum doesn't allow file attachments grrrrrr

 

The scenario is simple, so I must be doing something wrong!

- One single table in the model, entered using "Enter Data"

- 4 Dimensions each with slicers exposed on the report page:  Transport, Colour, Name, Date

- 1 Measure: A "Row" column which is always 1 (not really a practical use case but helpful for testing here)

- A simple intention to calculate the number of rows as a percentage of total rows ignoring "Colour" slicer BUT INCLUDING other slicers

- Two attempts to do this, one with REMOVEFILTERS and one with ALL:

-- "Transport % RemoveFilters = DIVIDE(  SUM('Table'[Rows]),   CALCULATE(  SUM('Table'[Rows]),  REMOVEFILTERS('Table'[Colour])  )

-- "Transport % All = DIVIDE(  SUM('Table'[Rows]),   CALCULATE(  SUM('Table'[Rows]),  ALL('Table'[Colour])  )

 

There are two active slicers in place that a user will change (but don't need to be changed to see the issue): Name=Kimberly,  Date=(a range).

With these slicers set, two rows remain from the underlying table: Kimberly has one Green Bike and one Red Bike and both the Transport % measures successfully return 50% (i.e. Kimberly has two bikes between the dates provided and each bike makes up 50% of the visible Bikes, regardless of colour)

However, if I then select a any Colour via the Colour slicer, then this number rises to 100% in both Measures. I'm trying to get these Measures to _ignore_ the Colour slicer.  How do I do this?

 

Interestingly enough, the same issue arises (i.e. Measures go to 50%) if I select "Bike" from the Transport slicer even though the remaining two rows are both Transport = "Bike" so setting the slicer doesn't change the resulting rows.

 

I've also tried the solution @DawdF95Rze mentioned above, by doing this, but no luck: 

Transport % RemoveFilters = DIVIDE(SUM('Table'[Rows]), CALCULATE(CALCULATE(SUM('Table'[Rows])), REMOVEFILTERS('Table'[Colour])))


I'm completely stumped, even after reading in depth articles like the ones below.

 

Any help would be greatly appreciated! It feels like such a simple issue and I can't see how I can achieve a interactive dashboard with LOD calculations without it 😞

https://www.sqlbi.com/blog/marco/2010/01/03/how-calculate-works-in-dax/

- https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

An update. It looks like the issue is the date slicer. 

I have three slicers in operation: transport, colour, and date. 

The colour slicer should be ignored from the CALCULATE function (using "REMOVEFILTERS('Table'[Colour]") within my measure.

If the date slicer uses the date HIERARCHY then my Measure behave properly (the filter context includes all slicers but ignores the Colour slicer).

If the date slicer use the full date value (not sure what the term is), then my Measure doesn't behave properly at all.

Anyone see this problem too?

DawdF95Rze
Frequent Visitor

I have two tables from Dynamics 365 CRM, these are Appointment and systemUser. I need that no matter how I use the department filter from the system user table, one user should not disappear from the table and the value of the sum of working time from the appoitment table should be calculated for him. Currently, his working time remains only in the table summary, while after filtering to the employee's department in which this user is not found, he evaporates from the table and his working time remains in the table summary.


After filtering by department's slicer I would like to have sum of worktime for userid 3 and his full name when I filtering by department whitch he doesnt belong to.

Zrzut ekranu 2020-12-23 141829.png

mesaure= CALCULATE( CALCULATE([workhour],all(),FILTER('is_pbi_dim_systemuser (2)','is_pbi_dim_systemuser (2)'[FullName]="Max W"),USERELATIONSHIP(is_pbi_dim_systemuser[SystemUserId],v_is_pbi_all_appointment[UserId])),REMOVEFILTERS('is_pbi_dim_systemuser (2)'[DeptShortName]))
Anonymous
Not applicable

Then you will have to cross check data model only 

How to do this?
My pbx:
https://ufile.io/tmi4edmv 

Anonymous
Not applicable

Hi @DawdF95Rze ,

 

You have to cross check with exesting Bookmarks also. Recently faced same issue after modifying bookmarks working fine in my local machine.

 

Thanks,

IVRavi.

amitchandak
Super User
Super User

@DawdF95Rze , How are using that. Refer my blog for usage -https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors