ALL() or REMOVEFILTERS() not working as expected

afaro — Mon, 13 May 2024 15:55:10 GMT

I have two slicers category & product.

Let's say the table looks something like this:

Customer	Category	Product	Revenue
X	C1	P1	10
X	C1	P2	30
X	C2	P3	50

I want to calculate calculate the remaining revenue based on slicer selection product.

So for example,for customer X, if I choose category as C1 and C2 in category slicer and I choose Product as P1 in product slicer then I should get something like this:

Customer	Total Revenue	Remaining Revenue
X	10	80

But I get something like this:

Customer	Total Revenue	Remaining Revenue
X	10	30

However, if I choose one product each from both categories, then the results are correct, so let's say I choose P1 and P3 in the product slicer, I get this:

Customer	Total Revenue	Remaining Revenue
X	60	30

However, if one product for both categories isn't included then the remaining doesn't work write.

The measures that I have written so far are which aren't giving me what I want are:

Total Revenue = CALCULATE(SUM(Table[Revenue])) Remaining Revenue = CALCULATE([Total Revenue], ALL(Table[Product]))

Any help would be appreciated.

Re: ALL() or REMOVEFILTERS() not working as expected

xifeng_L — Mon, 13 May 2024 17:02:35 GMT

Hi @afaro ,

Since both categories and products come from the same table, the Auo-Exist mechanism is triggered so that invalid filter combinations are removed.

For example, with C1 and C2 selected for categories and P1 selected for products, the following combinations would normally result:

Category=C1 && Product=P1 ||

Category=C2 && Product=P1

However, due to the Auto-Exist mechanism, the combination category=C2 && product=P1 is removed because product P1 does not belong to category C2.

So, it is required that the fields of both slicers cannot come from the same table, and a dimension table needs to be extracted to be used as the slicer's fields, e.g. you can extract category as the dimension table.

Here are the measure expressions and results:

Demo - ALL or REMOVEFILTERS not working as expected.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

topic Re: ALL() or REMOVEFILTERS() not working as expected in DAX Commands and Tips

ALL() or REMOVEFILTERS() not working as expected

Re: ALL() or REMOVEFILTERS() not working as expected