Flag Direct & Indirect Employees for the logged in Manager in employee table

[RahulYadav]

Hi,

I have applied Row level security using Employee hierarchy with PATH function and it is now showing Direct + Indirect employees reporting to the logged in manager.

I want to add a slicer which will filter Direct vs Indirect employees reporting to the managers. 

Is there a way to add a new column to employees table where we can flag Direct vs Indirect employees for the logged in manager.

Thanks,

Rahul

[Doylezeebeast]

Know this post is a few years old now but here goes. I was able to make the solution above work, for a Table layout. But I wanted to change this for say a card for the number of employees but I am not able to make this work. 
When creating the card I brought in the "DirectReportFliter" into the "Filters on the visual" (the same as I did for the table) but in the case of the Card. the option to change this to "is" = "True" is greyed out and just stuck on "contains". How can I make this work so that I have dynamic card that then shows the number of selected direct reports. Using the above example for viewing as the supervisor BCD@XYZ.COM i would expect to have "2" in the card. Thanks

[RahulYadav]

Hi ,

You can create a New Measure for showing Count of Direct Reports.

e.g.

Direct Reports = Calculate(count(Emp[Employee Number]) , [DirectReportFilter]=True)

Thanks,

Rahul

[Doylezeebeast]

Hi Rahul,
              Thanks for the quick reply. I tried to create a measure using your example above but I get an error message. This is the measure I wrote:

Measure = CALCULATE(COUNT(Data[Employee ID]),[DirectReportFilter]=True), 
I also tried:

Measure = CALCULATE(COUNT(Data[Employee ID]),[DirectReportFilter]="True")

But in both cases I have an error message underneath the formula bar that says:
A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
I am not sure what this means? / How we can fix this error?
Thank you

[v-ljerr-msft]

Hi @RahulYadav,

Could you be more precisely with the issue by post your table structures(including the relationships) with some sample/mock data, and the expected result? So that we can better assist on this issue. It's even better to just share a sample pbix file. 

You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[RahulYadav]

Hi @v-ljerr-msft,

Below is the sample hierarchy we have. I have created a Row Level security using PATH function and it is working correctly for "Direct + Indirect" Employees reporting to the logged in supervisor. But, I have got an request to add a slicer which will show two values "Direct" & "Indirect".

Sample Hierarchy:

 

Logic for Direct/Indirect Flag:

If User "BCD" logs in "EFG" & "FGH" shouldbe flag as "Direct" whereas JKL, KLM,LMN as Indirect.

Below is the sample pbix file. It has only one table from excel source. 

RLS Sample.zip

Thanks,

Rahul

[RahulYadav · Accepted solution]

Hi @v-ljerr-msft,
I have figured out a way for achieving this. I am posting it here so that it will be helpful for others.

Below are the steps I followed.
1. Create new table for creating a slicer for the flag for Direct Employees.

ForSlicer = union (row("DirectReports Flag","True"),row("DirectReports Flag","False"))

2. Add a new measure column in Employee table. This will return the flag we need.

Direct Reports = if(CALCULATE(max(Employee[Supervisor UserName]),filter(Employee,Employee[Supervisor UserName]=USERNAME()))<>"","True","False")

3. Add one more measure for creating a filter based on “ForSlicer” table.

DirectReportFilter = if(HASONEVALUE(ForSlicer[DirectReports Flag]),
if(values(ForSlicer[DirectReports Flag])="True",if([Direct Reports]="True","True",BLANK() ),"True"),"True")

4. Now add a visualization based on employee table and add below visual level filter.

    DirectReportFilter = True

5. Add a slicer based on “ForSlicer” Table.

I have attached the sample pbix file for the ready reference.

Below is the screenshot for the working of the logic:

For Direct+Indirect Employees

For Direct Employees only

 

RLS Sample_Solved.zip

Thanks,

Rahul

[Anonymous]

@RahulYadav & @v-ljerr-msft

What if we have multiple roles in our report, like as below example,

I have supervisor and unit head roles,

Now if you see for A2,

As a Supervisor, A2 having access to A5,A6,A10,A11,A12,A13 employees,

But as a Unit head, A2 having access to A8, A9, A14,A10,A11,A12,A13

Now whenever A2 loges in this report,

how can we show the UNION employees data i.e A5,A6,A8, A9,A10,A11,A12,A13,A14,  by including both roles employees.

Any thoughts,

Mohan V