cancel
Showing results for
Did you mean:

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

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

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.

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

4 REPLIES 4
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.

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.

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

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

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors