Filter Multiple Columns with Hierarchy Slicer

[andre2x]

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.

ID	Owner_Division	Owner_Area	Owner_Cohort	Originator_Division	Originator_Area	Originator_Cohort
1	a	b	c	d	e	f
2	d	x	y	a	x	y

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!

[ajaybabuinturi · Accepted solution]

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]

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]

@andre2x 

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