Matrix not Filtering by Higher-Level Query

[ivan_larson_cki]

I have a matrix showing various measures calculating sales for specific products. The products are listed as rows, and are broken out from categories. I have a higher-level Category query that i need for other visuals, but one of its functions is to slice this matrix. I only want to see the rows of the matrix that are relevant after applying the category slicer. How do I make this happen?

Current Visuals:

How it currently appears when sliced ("Hardware" and its associated rows should be hidden):

Categories:

Category	Sales Target
Food	1000
Hardware	800

Product Data:

Category	Product
Food	Burger
Food	Pizza
Hardware	Computer
Hardware	Desk
Food	Milk

Measures on Product Table:

Sales by Alpha = 0+CALCULATE(SUM(Sales[Amount]),FILTER('Sales','Sales'[Sales Person]="Alpha"))

Sales by Beta = 0+CALCULATE(SUM(Sales[Amount]),FILTER('Sales','Sales'[Sales Person]="Beta"))

Sales by Gamma = 0+CALCULATE(SUM(Sales[Amount]),FILTER('Sales','Sales'[Sales Person]="Gamma"))

Sales by Delta = 0+CALCULATE(SUM(Sales[Amount]),FILTER('Sales','Sales'[Sales Person]="Delta"))

Sales Total = 0+CALCULATE(SUM(Sales[Amount]))

Sales Data:

Product	Amount	Sales Person
Burger	20	Alpha
Pizza	40	Alpha
Computer	60	Alpha
Desk	50	Alpha
Milk	70	Beta
Burger	70	Beta
Pizza	30	Beta
Computer	80	Beta
Desk	60	Gamma
Milk	30	Gamma
Burger	50	Gamma
Pizza	40	Gamma
Computer	60	Delta
Desk	30	Delta
Milk	70	Delta

[smpa01]

@ivan_larson_ckithis is the direction of your table

which means the expanded table in DAX does

left outer with

categories to products

and

Categories-Products does left outer with Sales

Bring Category from category table and Product from Product Table in the matrix

For slicer bring category from Category table it should be able to slice

[ivan_larson_cki]

Hi @smpa01,

Thank you for your suggestion. In your matrices, food products are listed under hardware, and hardware products are listed under food. Is there a way to prevent that in the structure you're suggesting?

[smpa01]

Please change  to

Sales by Alpha = CALCULATE(SUM(Sales[Amount]),FILTER('Sales','Sales'[Sales Person]="Alpha"))

or

use

Alpha = CALCULATE(SUMX(Sales,Sales[Amount]),FILTER(Sales,Sales[Sales Person]="Alpha"))

[amitchandak]

Please check if the interaction is not switched off

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[ivan_larson_cki]

Hi @amitchandak ,

Thank you for your response. Interaction is on.

Also, here are the table relationships:

[v-lili6-msft]

hi @ivan_larson_cki 

If [Category] in the slicer is from 'Categories' table? try to use 'Categories'[Category] to create a slicer and try it again.

If you still have the problem, could you please share your sample pbix file for us have a test, there should be something wrong in other.

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Lin

[ivan_larson_cki]

Yes, the slicer is pulling from 'Categories'[Category].

I found a solution by switching the slicer to 'Products'[Category] and setting the cross-filter direction to "Both" on the relationship between 'Products' and 'Categories'. I realize using cross-filter directioning set to "Both" is not ideal, but I haven't noticed a substantial increase in load times yet.

[v-lili6-msft]

hi  @ivan_larson_cki 

Could you please share your sample pbix file for us have a test? there should be something wrong in others.

Regards,

Lin

[ivan_larson_cki]

Hi Lin,

Our organization's security settings don't allow sharing one drive files externally. Is there another way I can share it?