Forum Discussion
Hierarchy in Table.
Hi, MintuBaruah
in order to work with Hierarchy in DAX you need to utilize PATH function
and in order utilize PATH function your data table must assign EMP# and the Manager No# like following
| Role | Name | Status | Segment | Emp# | Manager |
|-------------------|---------|---------------------|---------------|------|---------|
| Associate Manager | Imtiyaz | Live_In Admin | Institutional | 4 | 1 |
| Director | Ryan | Live/In Dissolution | Institutional | 1 | |
| Associate Manager | Bhisham | Live/In Dissolution | Institutional | 2 | 1 |
| Director | Ryan | Live_In Admin | Private | 1 | |
| Associate Manager | Bhisham | Live/In Dissolution | Private | 2 | 1 |
| Director | Ryan | Live_In Admin | Institutional | 1 | |
| Associate Manager | Girish | Live_In Admin | Institutional | 3 | 1 |
| Director | Ryan | Live/In Dissolution | Institutional | 1 | |
| Associate Manager | Imtiyaz | Live/In Dissolution | Corporate | 4 | 1 |
Once you have that, create one calculated column as following
_path = PATH('Entity Table'[Emp#],'Entity Table'[Manager])
Count =
CALCULATE (
COUNTROWS ( 'Entity Table' ),
FILTER ( 'Entity Table', 'Entity Table'[Status] = "Live/In Dissolution" )
)
Count2 =
CALCULATE (
COUNTROWS ( 'Entity Table' ),
FILTER (
'Entity Table',
'Entity Table'[Status] = "Live/In Dissolution"
&& 'Entity Table'[Role] <> "Director"
)
)
_finalCount =
VAR _0 =
CALCULATE (
MAX ( 'Entity Table'[_path] ),
FILTER ( VALUES ( 'Entity Table'[Role] ), 'Entity Table'[Role] = "Director" )
)
VAR _1 =
CALCULATE (
[Count],
FILTER (
ALLEXCEPT ( 'Entity Table', 'Entity Table'[Segment] ),
PATHCONTAINS ( 'Entity Table'[_path], _0 )
)
)
RETURN
_1 + [Count2]
the Pbix is attached here
https://1drv.ms/u/s!AkrysYUHaNRvhcV78HPm3Rzop5yb_A?e=Aw5DZz
- MintuBaruah4 years agoHelper III
Hi smpa01
Thank you for the reply.
This solution is working but there is one problem I have to show this in a Matrix visualization and the Grand total is showing the wrong values (Please refer to Screenshot 1).
Screenshot 1:
Is there any solution to this issue as there is no option to disable this?
- smpa014 years agoCommunity Champion
MintuBaruah what is your desired output?
- MintuBaruah4 years agoHelper III
smpa01 , I just noticed in the Screenshot above the Director's total is incorrect but it is showing the correct values in my data.
Anyways, Grand total should also reflect the total of the "Director". As that is the total count.
In this case, the Director should have a total as:
Corporation: 1, Institutional: 3, Private: 1
Same total for Grand total
Thanks.