Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |