Forum Discussion

Purva's avatar
Purva
Frequent Visitor
2 years ago
Solved

Using Slicer to filter multiple columns with OR Logic

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

 

 

ItemCat1SubCat1Cat2SubCat2Cat3SubCat3Other Columns
1AA1BB5   
2BB5AA1BB5 
3GG3GG7BB5 
4XX9BB5PP6 
5PP6PP2   
6KK10K    

 

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!

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    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

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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