Filter on based on selected slicer value

[Anonymous]

Hi Experts,

I have a table for departments and department Hierarchies.

There is a slicer on Deparment name on a report page , i want the results on the  page to be filtered by the selected department and all its sub department .Kindly help.

Sample of Department Table and Department Hierarchy Tables given below.

Departments Table
Department 1
Department 1-2
Department 1-3
Department 1-4
Department 2
Department 2-2
Department 2-1
Department 4
Department 3
Department Hierarchy
Parent Child	Child Department
Department 1	Department 1-2
Department 1	Department 1-3
Department 1	Department 1-4
Department 2	Department 2-1
Department 2	Department 2-2
Department 4
Department 3

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a measure as below.

P = 
VAR a =
    SELECTEDVALUE ( department[department], BLANK () )
VAR parend =
    CALCULATE (
        MAX ( 'Department Hierarchy'[Parent] ),
        FILTER ( ALL ( 'Department Hierarchy' ), 'Department Hierarchy'[Child] = a )
    )
VAR par =
    CALCULATETABLE (
        VALUES ( 'Department Hierarchy'[Parent] ),
        ALL ( 'Department Hierarchy' )
    )
VAR child =
    CALCULATETABLE (
        VALUES ( 'Department Hierarchy'[Child] ),
        ALL ( 'Department Hierarchy' )
    )
RETURN
    IF (
        NOT ( ISFILTERED ( department[department] ) ),
        BLANK (),
        IF (
            a IN par
                && MAX ( 'Department Hierarchy'[Parent] ) = a,
            1,
            IF (
                a IN child
                    && MAX ( 'Department Hierarchy'[Parent] ) = parend,
                1,
                BLANK ()
            )
        )
    )

BTW, Pbix as attached.

[Anonymous]

Thanks for the quick turn around, it really helped.

As my requirements evolved , i had made a few modifications and craeted a Parent Department Column using logic you provided.

Parent Department Layer 2 =

VAR DEPT = Datacube[Department Code 1]

VAR DEPT1 = IF(Datacube[Department Layer]=3,CALCULATE (FIRSTNONBLANK (Departments[Department Code 2], 1 ),FILTER ( ALL ( Departments), Departments[Department Code 3]= FIRSTNONBLANK(Datacube[Department Code 1],1) )),"")

VAR DEPT2= IF(Datacube[Department Layer]=2,DEPT,DEPT1)

Return DEPT2