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 all
I am trying to findout Direct Reporting leader and indirect report to the leader for the below dataset. I tried different DAX expression but i didn't get the right result.
EmployeeID | LeaderID | EmployeeTitle |
1 | CEO | |
2 | 1 | VP |
3 | 2 | VP |
4 | 2 | Manager1 |
5 | 3 | Manager2 |
6 | 3 | Manager3 |
7 | 4 | Security engineer |
8 | 5 | Developer |
9 | 4 | Analyst |
10 | 6 | Developer II |
11 | 5 | Developer II |
12 | 8 | Manager4 |
When I looked for direct employee count for leaderID 2 by using the below DAX expression it is giving me the result as 5 but actually in the data set if we see it is only two people who directly report to employeeID 2 they are ( employeeid 3 and 4)
Also for indirect employee count for leaderID 2 i am getting the count as 12 but actually we have only 7 indirect employee's
Here are my queries which i used to create measures.
Direct Count:=
VAR level1 =
INTERSECT ( ALL ( [Employee ID] ), VALUES ( [Leader ID] ))
RETURN
CALCULATE ( COUNT ( 'Employee ID] ), ( level1))
Indirect Count:=
VAR level1 =
INTERSECT ( ALL ( [Leader ID] ), VALUES ( [Employee ID] ))
VAR level2 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level1)
)
VAR level3 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level2)
)
VAR level4 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level3)
)
VAR level5 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level4)
)
VAR level6 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level5)
)
VAR level7 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level6)
)
VAR level8 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level7)
)
VAR level9 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level8)
)
VAR level10 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level9)
)
VAR level11 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level10)
)
VAR level12 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level11)
)
RETURN
CALCULATE ( DISTINCTCOUNT ( [Employee ID] ), UNION ( level1, level2, level3, level4, level5, level6, level7, level8, level9, level10, level11, level12 ) )
Hi @vsk7775
You could refer to this similar thread.
Create measures
Direct =
VAR direct =
CALCULATETABLE (
VALUES ( 'Table'[EmployeeID]),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID] = MAX ( 'Table'[EmployeeID] ) )
)
RETURN
IF ( ISEMPTY ( direct ) = FALSE (), CONCATENATEX ( direct, 'Table'[EmployeeID], "," ) )
direct count = IF([Direct]<>BLANK(),LEN([Direct])-LEN(SUBSTITUTE([Direct],",",""))+1)
Indirect =
VAR direct =
CALCULATETABLE (
VALUES ( 'Table'[EmployeeID] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID]= MAX ( 'Table'[EmployeeID] ) )
)
VAR indirect =
CALCULATETABLE (
VALUES ( 'Table'[EmployeeID] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID] IN direct )
)
VAR child =
CALCULATETABLE (
VALUES ( 'Table'[EmployeeID] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID] IN indirect )
)
RETURN
IF (
ISEMPTY ( indirect ) = FALSE (),
CONCATENATEX ( UNION ( indirect, child ), 'Table'[EmployeeID] , "," )
)
indirect count = IF([Indirect]<>BLANK(),LEN([Indirect])-LEN(SUBSTITUTE([Indirect],",",""))+1)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-juanli-msft I tried this in my ssas tabular model cube to create this measure and I am getting all blanks in my sample file I have create the below Direct as new column and measure i tried both
Direct = VAR direct = CALCULATETABLE ( VALUES ( 'Table'[EmployeeID]), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID] = MAX ( 'Table'[EmployeeID] ) ) ) RETURN IF ( ISEMPTY ( direct ) = FALSE (), CONCATENATEX ( direct, 'Table'[EmployeeID], "," ) )
It exactly the same as what you created
Hi @vsk7775
Is your connection import or live connection?
Please check the difference between my pbix and yours and share a screenshot for further analysis.
Best Regards
Maggie
I have created the same measures in power bi for my sample data set and they are working in power bi But these are not working in SSAS Tabular model when I try to create do we need to change anything in the DAX queries I am seeing all Blanks in my tabular model measures. Also, in power bi we can see that measure as a column but not in SSAS Tabular.
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 |