Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 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 |
@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
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?
Please change to
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
Hi @amitchandak ,
Thank you for your response. Interaction is on.
Also, here are the table relationships:
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
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.
Could you please share your sample pbix file for us have a test? there should be something wrong in others.
Regards,
Lin
Hi Lin,
Our organization's security settings don't allow sharing one drive files externally. Is there another way I can share it?