RLS in Power BI: How to set the Slicer value to the ID of the log in user

[inikhilpv]

Hi,

I'm need to create a report with sales data. This report has a slicer with ID of the user.

The hierarchy of the users is given below. We have implemented dynamic RLS using the UserPrincipalName of the log in user.

In the above hierarchy, HQ is the Parent User (ID - 1). 2 Dealers are reporting to HQ ( ID - 2 & 3). SubDealer (ID-4) is reporting to Dealer (ID-2) and Subdealer (ID-5) is reporting to Subdealer (ID-4). Similarly SubDealer (ID-6) reports to Dealer (ID-3) and SubDealer(ID-7) reports to SubDealer(ID-6).

Requirement: When HQ logs in, he\she must be able to see his\her own data ie ID in slicer should be automatically set to 1. Apart from that HQ should be able to see the sales deatils of any of the Dealers\SubDealers reporting to HQ.

Similarly, when Dealer\Subdealer logs in, by deafult Slicer should be set to his\her ID and their own data must be visible. If they have any reportees, they must be able to see their data too by selecting the ID in the slicer. If no reportees, only their own data must be visible.

How to implement this using RLS. Is it possible to automatically set the slicer value to the ID of the user when he\she logs in so that the user can see his\her own data. Please advise.

Thank you

[v-veshwara-msft]

Hi @inikhilpv ,

We hope you’re doing well! We just wanted to follow up since we haven’t heard back from you. If your issue is all sorted, it’d be great if you could mark the reply as the solution and maybe drop a kudos--it helps others too. But if you still need assistance, just let us know--we’re happy to help!

Best,
Vinay.

[inikhilpv]

Hi @v-veshwara-msft ,

Apologies for the late reply. I was on vacation hence the delay.

The RLS is working perfectly the way you mentioned in the previous post. But still not able to find the solution for the other requirement ie when a user logs in, the user's dealer id must be set on the slicer by default.

for example, A is the dealer and B is the Subdealer who reports to A. So when A logs in, we get the aggregated value on the visuals, then A needs to select the dealer id A or B in the slicer to see their individual data. But the users want the slicer value to be set as A, when A logs in and only A's value should be displayed in the visuals. 

[v-veshwara-msft]

Hi @inikhilpv ,
First and foremost, I would like to apologize for the delay in getting back to you and sincerely thank you for your patience as we worked through this requirement.

After carefully analyzing the scenario and testing multiple approaches, we have identified that the behavior you are requesting is unfortunately not achievable due to RLS restrictions in Power Bi.
Explanation:

•  Slicers cannot show IDs for data that is not visible in the table due to RLS rules.
• If RLS restricts sub-dealer data visibility, the slicer cannot dynamically pre-populate or show sub-dealer IDs.
• RLS cannot "soft-hide" rows:
  There is no feature in Power BI where data can be hidden in visuals but still visible in slicers for selection.
  >So if a Dealer logs in reportees data cannot be hidden in table and still be visible in slicers for selection.
  >The slicer does not have a mechanism to override RLS rules temporarily to display restricted IDs.
  
  

Please feel free to share any additional thoughts or requirements, and we’ll be happy to work with you to find the best possible solution.

If this post helps, please consider accepting as solution and a kudos would be appreciated.

Best Regards,
Vinay.

[v-veshwara-msft]

Hi @inikhilpv ,
We haven’t heard back since our last response and wanted to check if your query has been resolved. If not, please feel free to reach out for further assistance. If it has been resolved, kindly mark the helpful reply as the solution to make it easier for others to find. A kudos would also be greatly appreciated!
Thank you.

[v-veshwara-msft]

Hi @inikhilpv ,
Thank you for posting your question in Microsoft Fabric Forum Community.
To achieve your query, the approach provided in the link provided by @amitchandak works perfectly.
Here is the detailed step by step process in Power Bi.
I have created two tables which replicates your data as below
Role Table

   
Sales

Step 1: Relationship between two Tables with common column (Id---->User)

Step 2: Create a calculated column by using the below DAX,

Path = PATH(RoleTable[ID],RoleTable[Parent ID])

Step 3: Create a Role for Dynamic RLS 
  Use the below DAX

PATHCONTAINS(

    RoleTable[Path],

    MAXX(

        FILTER(

            RoleTable,

            RoleTable[Email] = USERPRINCIPALNAME()

        ),

        RoleTable[ID]

    )

)


Step 4: Test the Result
Login with any user and you can see only the user's data and hierarchial data is visible.      

Here you can also see Slicer being automatically set to the Role obeying Dynamic RLS.

Hope this works for you,
If it does please consider accepting as solution to help others find it easily.
Thank you.

[inikhilpv]

Thank you @v-veshwara-msft . RLS works in the way mentioned above. One more requirement is when a dealer logs in, he\she must be able to see only their data in the table by default, not the aggregated data. When the dealer selects a sub-delaer id in the slicer only then that subdealer data must be displayed. 

[v-veshwara-msft]

Hi @inikhilpv ,

First and foremost, I would like to apologize for the delay in getting back to you and sincerely thank you for your patience as we worked through this requirement.

After carefully analyzing the scenario and testing multiple approaches, we have identified that the behavior you are requesting is unfortunately not achievable due to RLS restrictions in Power Bi.
Explanation:

•  Slicers cannot show IDs for data that is not visible in the table due to RLS rules.
• If RLS restricts sub-dealer data visibility, the slicer cannot dynamically pre-populate or show sub-dealer IDs.
• RLS cannot "soft-hide" rows:
  There is no feature in Power BI where data can be hidden in visuals but still visible in slicers for selection.
  >So if a Dealer logs in reportees data cannot be hidden in table and still be visible in slicers for selection.
  >The slicer does not have a mechanism to override RLS rules temporarily to display restricted IDs.
  
  

Please feel free to share any additional thoughts or requirements, and we’ll be happy to work with you to find the best possible solution.

If this post helps, please consider accepting as solution and a kudos would be appreciated.

Best Regards,
Vinay.

[TomMartens]

Hey @inikhilpv ,

next to the article, Amit is pointing to, please provide the DAX statement you are using to implement the dynamic RLS.

Regards,

Tom

[amitchandak]

@inikhilpv , Refer if the approch in RADACAD blog can help

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

[inikhilpv]

Thank you Amit. I'll go through it and will update you the result.