Need help with this DAX

[Anonymous]

Hello Everyone,

I need to find the no of employees under a manager?

Table: "emp_tbl"

No of Emps=

            COUNTROWS (FILTER(ALL ('emp_tbl', 

                                                       'emp_tbl'[Emp]= 'emp_tbl'[Emp]
                                                        &&  'emp_tbl'[Emp Type]= "New Joiners" )))

Do I need a self join or group by in DAX?

Thanks In Advance

[v-eqin-msft]

HI @Anonymous ,

Did I answer your question ? Please mark my reply as solution. Thank you very much~

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[v-eqin-msft · Accepted solution]

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

[Greg_Deckler]

@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.

https://docs.microsoft.com/en-us/dax/path-function-dax

[amitchandak]

@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

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi