Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors