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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

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
Super User
Super User

@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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors