topic Re: REMOVEFILTERS not working with chiclet slicer referring to another table for urls in DAX Commands and Tips

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

TimeaW — Tue, 30 May 2023 14:03:02 GMT

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').

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:

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.

The two tables are connected through Owner field, by Many to one.

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.

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

johnt75 — Tue, 30 May 2023 14:28:36 GMT

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.

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

TimeaW — Tue, 30 May 2023 14:46:12 GMT

Hi,

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

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.

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

johnt75 — Tue, 30 May 2023 15:12:51 GMT

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

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

TimeaW — Wed, 31 May 2023 10:32:04 GMT

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