cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fat_cat
Advocate II
Advocate II

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

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.

2 ACCEPTED SOLUTIONS
v-jayw-msft
Community Support
Community Support

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=po... 

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

@v-jayw-msft 

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

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

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=po... 

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

@v-jayw-msft 

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

amitchandak
Super User
Super User

@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() )

 

or

 

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

 

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

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors