Hide some (but not all) values based on organization hierarchy

[CSteff]

Reaching out to the wonderful Power BI community to help me solve this puzzle. 

• Note1 : the attached pictures represent the same dataset, just structured differently.
• Note 2: the numbers for sales managers are sum of their reports' number. Big Boss' numbers are the sum of sales managers numbers

I need the sales dashboard to show the sales data in such a way that:

• Salespeople cannot see Sales Targets and Actual Sales numbers of their peers,  yet should be able to see On Target %
• Salespeoples' respective managers (Sales Manager and Other Sales Manager), should be able to see all values for their reports, but not of their peer sales manager or his reports, yet, they should be able to see On Target %
• Big boss should be able to see everybody's numbers
• Obviously, Big Boss has his own, Even Bigger Boss, who should be able to see Big Boss's numbers. and so on

In essence, the target achievement (On Target %) should be visible throughout the organization (up and down and across peer levels), but the target and actual sales numbers should only be visible to the upper levels in the org hierarchy. 

Is it a data masking challenge, RLS, or something else?  

  

[v-eqin-msft]

Hi @CSteff ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[v-eqin-msft · Accepted solution]

Hi @CSteff ,

You could use PATH() function that can navigate through an unknown level of hierarchy based on an ID, and Parent ID structure. 

Please kindly take a look at these blogs for detailed help.

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

https://community.powerbi.com/t5/Community-Blog/Dynamic-Row-Level-Security-Rls-and-Company-Hierarchi...

https://www.bluegranite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

http://angryanalyticsblog.azurewebsites.net/index.php/2016/12/27/dynamic-rls-via-hierarchy-in-power-...

https://blog.jameyjohnston.com/row-level-security-in-power-bi-using-parent-child-hierarchies/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Eyelyn Qin