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
TimeaW
Frequent Visitor

REMOVEFILTERS not working with chiclet slicer referring to another table for urls

Hi,

 

REMOVEFILTERS is not working when used with a slicer which refers to another table.

 

I have a DAX formula which calculates the % distribution of USD Values for the selected owner within all owners.

 

USD 2015 = sum(Data[USD Value 2015])/(calculate(sum(Data[USD Value 2015]),REMOVEFILTERS(Data[Owner])))
 
So if i select an owner with a slicer, it will show the selected values % distribution within all Owners, not just the selected one (so Total is not 100%, but 'selected owner'/'all owners').
 
TimeaW_1-1685453215237.png
I have two data tables, 'Data' (the DAX command refers to this) and 'Logo'. On Data table I have the USD values per year per Owner, and on Logo table I have the Owners and url links:
TimeaW_3-1685453906703.png
The problem is, I want to use a Chiclet slicer, which uses the URLs from Logo table.

But if I filter on an owner, the dax command won't work, and it will show 100% everywhere. It's like the filtering is not working because the urls are taken from another table.

TimeaW_4-1685454389127.png
The two tables are connected through Owner field, by Many to one.
TimeaW_2-1685453727454.png

 I tried to change the DAX to refer to logo table, but it still shows 100% for everything.

%GT 2015 = sum(Data[USD Value 2015])/(calculate(sum(Data[USD Value 2015]),REMOVEFILTERS(Logo[Owner])))
 
Also tried to change Cross filtering direction to 'Both' but it still doesn't work.
 
Is there any way to make this DAX command work with Chiclet slicer?

 

 Many thanks in advance.
1 ACCEPTED SOLUTION

That should still work. What else appears under category in the matrix? Are any other filters being applied from other slicers ?

View solution in original post

4 REPLIES 4
TimeaW
Frequent Visitor

Hi,

 

Thanks for the quick answer. I've tried with that function but it still shows 100%.

TimeaW_0-1685457658946.png

 

I can't use columns from Logo table in the matrix, since it only has 3 columns, Owner, Url and Sorting order. And in the matrix I'm using Category and USD Value.

 

That should still work. What else appears under category in the matrix? Are any other filters being applied from other slicers ?

I've figured it out, there was probably a space in one of the Owner names in Logo table. After overwriting the names, it now works.

 

Thank you

johnt75
Super User
Super User

Try

USD 2015 =
SUM ( Data[USD Value 2015] )
    / ( CALCULATE ( SUM ( Data[USD Value 2015] ), REMOVEFILTERS ( Logo ) ) )

Make sure that you are using columns from the Logo table in your matrix as well as in the chiclet slicer.

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