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 ...
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

Sahir_Maharaj's avatar
Sahir_Maharaj
Super 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