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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Advocate III

## 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.
Advocate III

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

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

Advocate III

@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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors