Forum Discussion

andre2x's avatar
andre2x
Frequent Visitor
1 year ago
Solved

Filter Multiple Columns with Hierarchy Slicer

Hi, I am looking to multiple columns from the same table, with a single nested hierarchical slicer. The slicer is a 3 level filter containing Divison -> Area -> Cohort, with Cohort being the lowest level. In the table, I have records with an Owner and an Originator, and I've brought in their respective Divisions, Areas, and Cohorts. For the selected value in the slicer, records with an Owner OR Originator that match the selected value should be shown.

 

IDOwner_DivisionOwner_AreaOwner_CohortOriginator_DivisionOriginator_AreaOriginator_Cohort
1abcdef
2dxyaxy

 

For example, I'd like for the user to be able to select Division A, then rows 1 and 2 are displayed. If the user then drills down and selects Area B, only row 1 will be displayed, etc.

Please let me know if you have any questions and thanks in advance!

Need Help
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    Hi andre2x ,

    I would like to suggest to follow below steps to get expected results.
    Step1: Create HierarchyTable using below DAX

    HierarchyTable = 
UNION(
    SELECTCOLUMNS(Data, "Division", Data[Owner_Division], "Area", Data[Owner_Area], "Cohort", Data[Owner_Cohort]),
    SELECTCOLUMNS(Data, "Division", Data[Originator_Division], "Area", Data[Originator_Area], "Cohort", Data[Originator_Cohort])
)

    Step2: Create IsInScope measure for selected Division, Area and Chort. It ensures that whether the selected values are in scope or not.

    IsInScope = 
Var SelectedDivision = SELECTEDVALUE(HierarchyTable[Division])
Var SelectedArea = SELECTEDVALUE(HierarchyTable[Area])
Var SelectedCohort = SELECTEDVALUE(HierarchyTable[Cohort])
Var Result =
IF(
    -- Check if Division is selected and matches either Owner or Originator
    (
        ISBLANK(SelectedDivision) || 
        MAX(Data[Owner_Division]) = SelectedDivision || 
        MAX(Data[Originator_Division]) = SelectedDivision
    ) &&
    -- Check if Area is selected and matches either Owner or Originator
    (
        ISBLANK(SelectedArea) || 
        MAX(Data[Owner_Area]) = SelectedArea || 
        MAX(Data[Originator_Area]) = SelectedArea
    ) &&
    -- Check if Cohort is selected and matches either Owner or Originator
    (
        ISBLANK(SelectedCohort) || 
        MAX(Data[Owner_Cohort]) = SelectedCohort || 
        MAX(Data[Originator_Cohort]) = SelectedCohort
    ),
    1, -- Show row if conditions match
    0  -- Hide row otherwise
)
RETURN
Result

    Step3: Add the Division, Area and Chort values to the visual and add IsInScope to the filter pane and set the value is 1.

    By using above approach you will get the desired output. Also I am attching .pbix file for your reference.
    https://drive.google.com/file/d/1LMc_P0aEqhC7VpNntRFlkPmZqP8jZIZk/view?usp=drive_link 

    Thanks,

    If the solution helps, please Like👍 and mark it as Accepted Solution✅ so it may help others, if anyone facing same questions/issues.

3 Replies

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Super User
    1 year ago

    Hi andre2x ,

    I would like to suggest to follow below steps to get expected results.
    Step1: Create HierarchyTable using below DAX

    HierarchyTable = 
UNION(
    SELECTCOLUMNS(Data, "Division", Data[Owner_Division], "Area", Data[Owner_Area], "Cohort", Data[Owner_Cohort]),
    SELECTCOLUMNS(Data, "Division", Data[Originator_Division], "Area", Data[Originator_Area], "Cohort", Data[Originator_Cohort])
)

    Step2: Create IsInScope measure for selected Division, Area and Chort. It ensures that whether the selected values are in scope or not.

    IsInScope = 
Var SelectedDivision = SELECTEDVALUE(HierarchyTable[Division])
Var SelectedArea = SELECTEDVALUE(HierarchyTable[Area])
Var SelectedCohort = SELECTEDVALUE(HierarchyTable[Cohort])
Var Result =
IF(
    -- Check if Division is selected and matches either Owner or Originator
    (
        ISBLANK(SelectedDivision) || 
        MAX(Data[Owner_Division]) = SelectedDivision || 
        MAX(Data[Originator_Division]) = SelectedDivision
    ) &&
    -- Check if Area is selected and matches either Owner or Originator
    (
        ISBLANK(SelectedArea) || 
        MAX(Data[Owner_Area]) = SelectedArea || 
        MAX(Data[Originator_Area]) = SelectedArea
    ) &&
    -- Check if Cohort is selected and matches either Owner or Originator
    (
        ISBLANK(SelectedCohort) || 
        MAX(Data[Owner_Cohort]) = SelectedCohort || 
        MAX(Data[Originator_Cohort]) = SelectedCohort
    ),
    1, -- Show row if conditions match
    0  -- Hide row otherwise
)
RETURN
Result

    Step3: Add the Division, Area and Chort values to the visual and add IsInScope to the filter pane and set the value is 1.

    By using above approach you will get the desired output. Also I am attching .pbix file for your reference.
    https://drive.google.com/file/d/1LMc_P0aEqhC7VpNntRFlkPmZqP8jZIZk/view?usp=drive_link 

    Thanks,

    If the solution helps, please Like👍 and mark it as Accepted Solution✅ so it may help others, if anyone facing same questions/issues.

    • andre2x's avatar
      andre2x
      Frequent Visitor
      1 year ago

      This is working for me, thank you very much! Is there a workaround to applying this as a page-level filter? Currently I am applying IsInScope = 1 individually to each visual.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    andre2x 

    is this the table your sample data? what is the table for  hierarchical slicer? could you pls also provide that table?