Employee Manager - Calculated column

[Anonymous]

Dear All,

Looking for your help in below scenario either in calculated column or in M-query form.

I have the details as Ename, Role & Mname.

This is a basic query in SQL i.e. a manager can be an employee but all employee cannot be manager. I need to find the list of employees under each manager.

So I need to derive a new column in PowerBI which might say if title is manager or not.

Further I can apply row level security for each manager so they can see only the employee under them.

Hope you could help me in deriving the last column "Title".

Regards,

Abhi

[Fowmy]

@Anonymous 

Not clear about the requirement and the output expected. 

is it a Column like:

Title = IF( [Role] = "Manager", Blank(), "SM")

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

[Anonymous]

I dont think it will work.

Basically I need to display the list of employees under each manager....

[Fowmy]

@Anonymous 

Add this as a new column:

Title = 
VAR _M = 
    CALCULATETABLE( 
        VALUES('Table'[Ename]),
        'Table'[Role] = "Manager"
    )
RETURN

IF( [Ename] IN _M, 
    CONCATENATEX(
        FILTER( 'Table', 'Table'[Mname] = EARLIER('Table'[Ename])),
        [Ename], "|")

)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

This is not the output I am looking for. 

I need,

[Fowmy · Accepted solution]

@Anonymous 

I am not sure how you will utilize this field, however, I have modified the code, please check now.

Title = 
VAR _M = 
    CALCULATETABLE( 
        VALUES('Table'[Ename]),
        'Table'[Role] = "Manager"
    )
RETURN

IF( NOT [Ename] IN _M, "SM", BLANK()) 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn