Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter on based on selected slicer value

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 ChildChild Department
Department 1Department 1-2
Department 1Department 1-3
Department 1Department 1-4
Department 2Department 2-1
Department 2Department 2-2
Department 4 
Department 3 

 



Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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