How to apply specific RLS to different visuals?

[GQ_88]

Hi! I would like to apply different RLS based on certain visuals. When having 2 visuals, Visual 1 should only show the viewer's own data. Visual 2 should show them their data and everyone who reports to them.

Model Set Up

I have a dataset with the employee, their manager, the management ladder between employee and manager, and a metric. RLS has been applied to this dataset such that if the viewer (e.g. USERPRINCIPALNAME()) is w/in the Management Ladder, then they can see that data. In this example, employee A can see all of the data, but B, C, D would see only their row. Because of this current set up, it seems like I can accomplish Visual 2 in the screenshot. 

Desired Outcome

If I wanted to make visual 1 just show only the viewer's data, how would I set that up? Currently, if employee A was viewing, Visual 1 aggregates everyone's data for them. 

Since RLS is set at the semantic model level, I'm unsure of how to approach the visual settings...especially considering that USERPRINCIPALNAME() and related DAX are measures, so I may not be able to apply them as a visual filter. 

[v-huijiey-msft]

Hi @GQ_88 ,

Thanks for the reply from lbendlin .

To implement different filters for each of the two visual objects, you can create two measures and then apply them to the Filter at the visual object level. creating an RLS directly affects all the visual objects, not just a single visual object.

Please follow my steps.

First, here is my test data:

Then, create two measures:

MEASURE =
IF ( MAX ( 'Table'[EMPL] ) = USERPRINCIPALNAME (), 1, 0 )

MEASURE 2 =
IF (
    MAX ( 'Table'[EMPL] ) = USERPRINCIPALNAME ()
        || MAX ( 'Table'[Manager] ) = USERPRINCIPALNAME (),
    1,
    0
)

Create two visual objects, set the screening effect of visual object 1 to MEASURE = 1, and set the screening effect of visual object 2 to MEASURE 2 = 1.

The final page visualization is shown below. At this point, you can observe that Visual 1 displays only the viewer's data, while Visual 2 displays their data and all the people reporting to them.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[GQ_88]

Hi Yang! Thank you for sharing this solution; it's promising. I was following the steps for Visual 1 & build Measure 1. When I try to filter to "is 1", the filter on the visual doesn't let me click on the drop down option:

I tried following this: Solved: Re: Using measure as a filter - Microsoft Fabric Community but it seemed like my DAX format was correct; did you also have it set to "Whole Number" as well?

Additionally, is there another way to do this instead of using MAX? When I was debugging, it seemed like the MAX returns the max of the dataset, so in this case, it'll only work if the employee == E. If the user viewing wasn't E, the boolean would be false since it's E [max employee in this dataset] == [another employee letter who's viewing].

Thank you very much again!

[v-huijiey-msft · Accepted solution]

Hi @GQ_88 ,

I can't reproduce your mistake.

My measures are of the WHOLE NUMBER type.

There is another way to create two new measures:

CurUserMetric = 
CALCULATE (
    SELECTEDVALUE ( 'Table'[Metric] ),
    FILTER ( 'Table', 'Table'[EMPL] = USERPRINCIPALNAME () )
)

CurUserAndManagerMetric =
CALCULATE (
    SELECTEDVALUE ( 'Table'[Metric] ),
    FILTER (
        'Table',
        SELECTEDVALUE ( ( 'Table'[EMPL] ) ) = USERPRINCIPALNAME ()
            || SELECTEDVALUE ( ( 'Table'[Manager] ) ) = USERPRINCIPALNAME ()
    )
)

Dragging these two measures to the two visual objects to replace the [Metric] also fulfills the requirement, and this method does not need to set the Filter.

If the problem is still not solved, please provide a Power BI Desktop file in progress (with sensitive information removed) that fully covers your issue or question in a usable format (not a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive (set up public access), SharePoint, or a Github repository, and then share the URL of the file.

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[GQ_88]

Hi Yang! Appreciate your help & alternatives here. Creating new DAX that's leveraging the FILTER() statement did work for our use case. Wishing the visual filter one did too, but this helps us continue in our build. Just marked your comment as the solution. 🙂

[lbendlin]

.especially considering that USERPRINCIPALNAME() and related DAX are measures, so I may not be able to apply them as a visual filter. 

That is no longer a constraint. Measures can be visual filters.