topic Re: Facts table filtered by 2 slicers built on the same table - problem with calculation (ALL) in DAX Commands and Tips

Facts table filtered by 2 slicers built on the same table - problem with calculation (ALL)

fat_cat — Mon, 30 May 2022 09:33:42 GMT

I'm trying to do something which seemed as an extremely simple task, but somehow I can't make it work properly.

Here is my issue:

I have two tables: 'Products' and 'Facts'

I have two slicers built on a Products table:

- first one: filters by Segmentation

- the other one: filters by Brand

And I have two measures:

- [Sales_Selected_Brand] = SUM(FACTS[Sales]) - which is obvious

- [Sales_All_Brands] = CALCULATE([Sales_Selected_Brand], ALL('Products'[Brand]) )

this one was supposed to sum up all sales for selected segments in the first slicer, regardless of Brand selection in the other slicer. But here is a problem: [Sales_All_Brands] includes sales for all brands, but only for those segments in which selected brand is present in Facts table.

Example: I have segments A,B,C,D selected in segmentation slicer, and I have Brand1 selected in second slicer (but it only has products / sales in segments A,B,C). I want [Sales_All_Brands] to show the sum of segments A,B,C,D for all brands. But it returns the sum of sales for all brands, but only for segments A,B,C.

I guess the source of the issue is that both slicers are built on the same Product table. But it must be a way to make it work?

PS. Here is a dummy data example with the issue visible.

Re: Facts table filtered by 2 slicers built on the same table - problem with calculation (ALL)

amitchandak — Mon, 30 May 2022 11:38:22 GMT

fat_cat , In case of dimension filter - Single directional. The fact will continue to get filtered from other dimensions and fact filter

The true all will be

Sales_All_Brands = CALCULATE([Sales_Selected_Brand], ALL() )

Sales_All_Brands = CALCULATE([Sales_Selected_Brand], ALL(Fact) )

Re: Facts table filtered by 2 slicers built on the same table - problem with calculation (ALL)

fat_cat — Mon, 30 May 2022 12:27:23 GMT

amitchandak - thank you for you response.

However, your proposed solutions are not exactly what I'm looking for. Your formulas ignore both slicers. What I want do, is to calculate [Sales_All_Brands] with only Brand slicer ignored, but with preserved filters from the second slicer (Segmentation).

Re: Facts table filtered by 2 slicers built on the same table - problem with calculation (ALL)

Anonymous — Thu, 02 Jun 2022 08:55:06 GMT

Hi fat_cat ,

There is a trick that you could disable the interaction between [Brand] slicer and the result visual.

Please take a look at this document.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions?tabs=powerbi-desktop

Best Regards,

Jay

Re: Facts table filtered by 2 slicers built on the same table - problem with calculation (ALL)

fat_cat — Thu, 02 Jun 2022 17:05:00 GMT

Anonymous

Hi Jay, thank you. Indeed, it does the trick for this simple example, as I described it (when you only need to display the result)

However in my case (in the actual model I was working on), it was more complex - I needed both measures ([Sales_Selected_Brand] and [Sales_All_Brands]) to be calculated properly and used together for further calculations, so I ended up with different solution.

I wasnt able to find a way to do it in DAX, so I decided to modify my model and separate Brand hierarchy from the Segmentation hierarchy (instead of having one Product table with all product info, I made two separate tables, which can now be filtered independently).