Using Slicer to filter multiple columns with OR Logic

[Purva]

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!

[Sahir_Maharaj · Accepted 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