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.
Hello @all,
Entity Table:
Role | Name | Status | Segment | |||
Associate Manager | Imtiyaz | Live_In Admin | Institutional | |||
Director | Ryan | Live/In Dissolution | Institutional | |||
Associate Manager | Bhisham | Live/In Dissolution | Institutional | |||
Director | Ryan | Live_In Admin | Private | |||
Associate Manager | Bhisham | Live/In Dissolution | Private | |||
Director | Ryan | Live_In Admin | Institutional | |||
Associate Manager | Girish | Live_In Admin | Institutional | |||
Director | Ryan | Live/In Dissolution | Institutional | |||
Associate Manager | Imtiyaz | Live/In Dissolution | Corporate |
I have a data set that is similar to the above table.
I have to find the Count number of rows with the status "Live/In Dissolution".
This should be shown for the Different Segments
But the issue is that there is a hierarchy in the data i.e, "Associate Manager" comes under "Director". So the Count of "Associate Manager" should also be added to the count of "Director".
I am trying to show this with the help of Matrix, but the "Director" in this case "Ryan" is not showing the added values.
This Is the Measure I am using to Count Rows:
CALCULATE(
COUNTROWS('Entity Table'),
FILTER('Entity Table','Entity Table'[Status]="Live/In Dissolution")
)
Please help resolve this issue.
Thank you.
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
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?
@MintuBaruah what is your desired output?
@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.
@MintuBaruah can you please confirm if you desire to see 1 in the highlighted section? Please explictly tell me what is your final desired output either through an expliict screenshot or table
@smpa01 , yes.
For Director- Corporation: 1, Institutional: 3, Private: 1
For Total- Corporation: 1, Institutional: 3, Private: 1
Thanks.
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |