How to count the number of times a column value appears in another

Power BI
1

I have a table of employee data that has the following columns

Emp_id
Emp_name
emp_hierarchy
Direct Reports

Direct reports will tell you how many people report to that person but I am needing a way to see how many people are under that person in the organization.

So in my example, if emp_id is M1 then it would have only 1 direct report but 5 “underlings”. M3 has 2 direct reports but 3 “underlings”

The underlings column was added by me and does not exist in my real data set so I have to find a way to calculate it, either by adding a calculated column or making a new measure. The real dataset is an SQL table so if there’s a way to do it with a custom query that would work as well.

BI-Test.pbix (27.3 KB)

2

Welcome to the forum @OKE22167 :slight_smile:

Bumping this post for more visibility.

3

Hi @OKE22167

Please try below DAX to create a new Calculated Columns as done in attached pbix.

BI-Test.pbix (28.7 KB)

Underlinings_New = 

var a  = Sheet1[emp_id]
var underling = COUNTROWS(filter(Sheet1,search(a,Sheet1[emp_id_hierarchy],1,0))) 
return
if(ISBLANK(underling),0,underling)

Thanks
Ankit J

4

You can try using the PATH DAX function to arrive at Underling Headcount. Please refer to the below blog post link for details.
Working with organizational hierarchy in Power BI - Nathan Prats

5

Thanks for the help @ankit and @pranamg! Hope the suggestions above help solve your query @OKE22167

Please don’t forget to tag the post that solved your query as “solution”.

Thanks!

6

Hi @OKE22167, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,We hope you’ll give your insights on how we can further improve the Support forum. Thanks!