Forum Discussion
Need help with this DAX
Hello Everyone,
I need to find the no of employees under a manager?
Table: "emp_tbl"
&& 'emp_tbl'[Emp Type]= "New Joiners" )))
Do I need a self join or group by in DAX?
Hi Anonymous ,
According to my understanding, you want to count the number of Emp under each manager when their type is “New Joiners”, right?
You could use the following formula:
No of Emps = COUNTROWS ( FILTER ( ALL ( emp_tbl ), emp_tbl[Manager] = MAX ( emp_tbl[Manager] ) && emp_tbl[Emp type] = "New Joiners" ) )My visualization looks like this:
Is the result what you want? If not, please upload some data samples and expected output.
Please do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
4 Replies
Anonymous , you need to create an employee table and join it both with Emp and manager. Keep emp join active and manager join inactive and when to want to know employee under manager the activate the join using userelation
Anonymous - What is your expected output from your sample data? This may be a transitive closure situation, I remember doing something similar to this in the past and I believe it was a spin on transitive closure. The specific problem was related to calculating commissions for a sales person hierarchy.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Transitive-Closure/m-p/783828#M388
Basically you have to walk the hierarchy unless you are just going for direct reports. The PATH functions may be of use.
Hi Anonymous ,
According to my understanding, you want to count the number of Emp under each manager when their type is “New Joiners”, right?
You could use the following formula:
No of Emps = COUNTROWS ( FILTER ( ALL ( emp_tbl ), emp_tbl[Manager] = MAX ( emp_tbl[Manager] ) && emp_tbl[Emp type] = "New Joiners" ) )My visualization looks like this:
Is the result what you want? If not, please upload some data samples and expected output.
Please do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
