Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vhatp
Frequent Visitor

RLS combined with a field parameter / slicer?

HI, I've done a lot of searching, but to no avail. I wonder if it's possible to have dynamic RLS even more dynamic so that it would change upon user selection. I need the user to be able to select, which attribute should be filtered by RLS.

 

Let's say there is a user table with a City, where the user works.

The fact table contains product data with two links to a city dimension - e.g. "City of origin" and "Destination City". It does not matter much to me, whether I use a single dimension table of cities or multiple equivalent ones - the latter makes things simpler.


So far, I am able to set up a matrix to summarize product data by a City and have a slicer to switch the respective context (matrix rows) - either City of origin or Destination City using a Field Parameter, lets call it "City type". 

The user should see all items relevant for his city. That is in one case, the items from his city, in the other case, items to his city. Some of the items apply for both.

 

I am able to link the the User City to both the attributes via RLS, I am just not able to make the selection part. If I apply the filter to both the City attibutes simultaneously, only rows that evaluate as true for both the criteria are kept. That is not intended. Therefore I think I need some IF logic based on the slicer value. But no matter what I tried, It seems that the RLS filter is not capable re-calculating and re-applying itself after loading the report, and just thinks there is no value selected by the slicer - no city at all.

I tried setting up the RLS filters for the respective dimensions as well as the fact table, using relationships and dax filters, nothing got it working so far. I tend to believe, it is not possible, but don't want to.


I'd be thankful for any hint, which approach to use in order to achieve this.
Thank You very much in advance.

2 REPLIES 2
AntrikshSharma
Community Champion
Community Champion

@vhatp Security of any kind becomes active the moment user connects to the model, if a user is added to a role then as soon as they open the report the security is active, so there is no concept of conditional security once the report is open, that means they can't browse or switch roles after opening a report.

Thank You for the answer, I have realised that and came to a solution based on measures.
I let the RLS filter the user table by USERPRINCIPALNAME(), that is, select one city.
All relevant measures delivering values into visuals have been modified to incorporate the currently selected Field attribute via IF-THEN-ELSE condition within CALCULATE filter to filter either by city of origin or by destination. It's a bit of a pain, but works.
Another idea was to convert the fact tables to calculated tables based on that field parameter, but I guess that would not be the most effective solution to common scenarios, would it?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors