Search code examples
filterpowerbislicersrow-level-security

PowerBI RLS on slicer in dashboard

enter image description here

Hello everyone,

The screenshot above is my data model for PowerBI dashboard. I already set the "Cross filter direction" between City lookup and Restaurant Inspection Data to "Both".

I applied RLS on the Inspector Lookup table as shown in the screenshot below:

enter image description here

When I tried to test the RLS by using "View as" in PowerBI, most of the DAX measures and visuals work well. However, I realized that the filter from RLS did not apply on the City Lookup table even thought I already set the cross filter direction to "Both". Below are the 2 screenshots before I applied RLS and after I applied RLS:

Before applying RLS:

enter image description here

After applying RLS:

enter image description here

As you can see in the screenshot above, after I applied the RLS, the bar chart and column chart at the left hand side were filtered but City slicer and table at the right hand side still remain the same. What I need to do is select the Inspector Name in the slicer, then the table and city slicer only reflected accordingly as shown in the screenshot below:

enter image description here

May I know is there any way that I can avoid this situation? By right, if I want to apply RLS, there is no Inspector Name slicer in my dashboard since each inspector is only able to see his own data so the inspector slicer is useless to put in the dashboard. I want the City table are able to affected by the RLS as well, any help or advise will be greatly appreciated!

Solution

  • By default RLS filters do not traverse backwards even when a relationship is configured for bidirectional cross-filtering.

    If you want that you simply set the "Apply security filter in both directions" option on the bidirectional cross-filtering relationship.

    enter image description here

    But it's not a best-practice to use bi-directional relationships to filter slicers. As Alberto Ferrari, an absolute authority, says:

    Implementing bidirectional cross-filter for the purpose of syncing slicers is definitely a bad idea. After all, if you need to kill ants in the yard, you do not turn on the Death Star.

    Bidirectinoal relationships and ambiguity in DAX

    One alternative is use a visual-level filter on the slicer instead: Filter Slicers Without using Bi-directional relationships

    If you really need to secure the city dimension, use a separate security table. This can be derived from your fact table, eg

    CitySecurity = DISTINCT(SELECTCOLUMNS(NATURALINNERJOIN(Inspector,Data),"CityId",[CityId],"Email", [Email]))

    Set RLS on that table and configure it to apply security filters to the City table though a bidirectional relationship, which is the normal RLS security pattern for dimension tables.

    enter image description here