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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jzeosky
New Member

Measure using REMOVEFILTER Behaves Unexpectedly

Hello,

I'm seeing some strange behavior with one of my measures that uses REMOVEFILTER. I'm not sure how to explain it clearly so here's a simple example I've setup that shows the behavior. I have a simple table with three columns:

 

jzeosky_1-1616774108653.png

 

Similarly I have 3 slicers that slice the data by each of the columns:

 

jzeosky_2-1616774156019.png

 

I want to calculate the percentage of the data that falls within the the FirstReview slicer range. So for example if Date is set to a range of 1-2 we'll have a total of 6 entries. If then FirstReview is set to 1-3, there are only 3 entries in that range. The calculated percentage should then be 50% (3 in FirstReview range out of a total of 6 from all other slicing).

 

I have 3 measures defined as follows:

 

ReviewStaticCount = CALCULATE(DISTINCTCOUNT(Sheet1[ID]), REMOVEFILTERS(Sheet1[FirstReview]))
FilteredIDCount = DISTINCTCOUNT(Sheet1[ID])
%ID = ([FilteredIDCount]/[ReviewStaticCount]) * 100
 
My expectation is that ReviewStaticCount should calculate the number of entries independent of the range set on the FirstReview slicer, but respecting all other slicers. FilteredIDCount should be the number of entries after all slicers are applied. %ID should then be the final percentage I want to calculate.
 
This seems to work if I only adjust the FirstReview slicer, see below:
jzeosky_3-1616774708530.png

 

However if I also adjust the Date slicer, the calculated percentage will change wildly with the selected range. Notice in the example below if I set Date to be 1-2 and FirstReview to be 1-4 the resulting percentage is calculated properly:

jzeosky_4-1616774839581.png

 

But if I change FirstReview to 1-3 ReviewStaticCount is no longer calculated properly (it ignores the values where Date=2) and the resulting percentage is incorrect (we would expect 50%).

jzeosky_5-1616774939421.png

Am I misunderstanding how REMOVEFILTER should behave in this context?

 

Thanks in advance for the help!

 

 

1 ACCEPTED SOLUTION
JW_van_Holst
Resolver IV
Resolver IV

This was a real tricky one. I don't think you misunderstand the REMOVEFILTER. The problem is that the slicer on FirstReviews cross filters the slicer on Date. So if your slicer on FirstReview is below 7 Data 3 will be cross filtered. You can overcome this problem bij creating a separate Data table with the distinct values and create a relationship with you table. Use the table with the distinct values as slicer. This will not be cross filtered by FirstReview because it is on the one side (no filter propagation).

Good Luck,

//JW

View solution in original post

2 REPLIES 2
JW_van_Holst
Resolver IV
Resolver IV

This was a real tricky one. I don't think you misunderstand the REMOVEFILTER. The problem is that the slicer on FirstReviews cross filters the slicer on Date. So if your slicer on FirstReview is below 7 Data 3 will be cross filtered. You can overcome this problem bij creating a separate Data table with the distinct values and create a relationship with you table. Use the table with the distinct values as slicer. This will not be cross filtered by FirstReview because it is on the one side (no filter propagation).

Good Luck,

//JW

I tried your solution and it worked! I can't say I really understand the concept of cross-filtering, but I can learn more about that on my own time. For the time being this is a workaround that I can live with.

 

Really appreciate the help!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors