Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have data in belwo format in one of my tables. For each item, there can be multiple Categories, and subcategories-
I want to implement a slicer such that if a user selects a particular category, or subcategory it would give item details, where either the selected category is in cat1, cat2 or cat3, and selected subcategory is in subcat1, subcat2, or subcat3
Item | Cat1 | SubCat1 | Cat2 | SubCat2 | Cat3 | SubCat3 | Other Columns |
1 | A | A1 | B | B5 | |||
2 | B | B5 | A | A1 | B | B5 | |
3 | G | G3 | G | G7 | B | B5 | |
4 | X | X9 | B | B5 | P | P6 | |
5 | P | P6 | P | P2 | |||
6 | K | K10 | K |
So, here If user want's to see items for category B, subcategory B5, below items should be displayed:-
Item1, Item2, Item3, Item 4
Could you please suggest the most optimal way to perform this as this is a massive dataset.
Thank you in advance!
Solved! Go to Solution.
Hello @Purva,
Can you please try this DAX for Dynamic Filtering:
SlicerTable = DISTINCT(UNION(SELECTCOLUMNS(YourTable, "CatOrSubCat", YourTable[Cat1]), SELECTCOLUMNS(YourTable, "CatOrSubCat", YourTable[SubCat1]), ...))
ItemFilterMeasure =
VAR SelectedCatOrSubCat = SELECTEDVALUE(SlicerTable[CatOrSubCat])
RETURN
IF(
COUNTROWS(
FILTER(
YourTable,
YourTable[Cat1] = SelectedCatOrSubCat ||
YourTable[SubCat1] = SelectedCatOrSubCat ||
YourTable[Cat2] = SelectedCatOrSubCat ||
YourTable[SubCat2] = SelectedCatOrSubCat ||
YourTable[Cat3] = SelectedCatOrSubCat ||
YourTable[SubCat3] = SelectedCatOrSubCat
)
) > 0, 1, 0
)
Note: You'll first need to normalize your data by unpivoting the category and subcategory columns
Hello @Purva,
Can you please try this DAX for Dynamic Filtering:
SlicerTable = DISTINCT(UNION(SELECTCOLUMNS(YourTable, "CatOrSubCat", YourTable[Cat1]), SELECTCOLUMNS(YourTable, "CatOrSubCat", YourTable[SubCat1]), ...))
ItemFilterMeasure =
VAR SelectedCatOrSubCat = SELECTEDVALUE(SlicerTable[CatOrSubCat])
RETURN
IF(
COUNTROWS(
FILTER(
YourTable,
YourTable[Cat1] = SelectedCatOrSubCat ||
YourTable[SubCat1] = SelectedCatOrSubCat ||
YourTable[Cat2] = SelectedCatOrSubCat ||
YourTable[SubCat2] = SelectedCatOrSubCat ||
YourTable[Cat3] = SelectedCatOrSubCat ||
YourTable[SubCat3] = SelectedCatOrSubCat
)
) > 0, 1, 0
)
Note: You'll first need to normalize your data by unpivoting the category and subcategory columns
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
96 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |