Multiple Role-Playing Dimension and RLS

[jmunn]

Hi, 

I've browsed around existing questions for a while but so far haven't seen my issue, and ChatGPT is giving me the runaround so far, so figured I would post my question. It has to do with the intersection of multiple-role-playing dimensions and RLS. 

In my data model, I have a fact table in which location is used in two ways - let's say location_departure and location_arrival. I have a separate dimension table containing all the location values. Since I need to filter/interact with the fact table using location across both fields and can't have two active relationships, and because this dimension table is relatively small, I copied it using Reference in Power Query, so now have two identical tables that can filter on location_departure and location_arrival, respectively. So far so good, and this works beautifully for a user with access to all the data.

However, I need RLS to restrict users to only allowable locations. So I've added a role using USERPRINCIPALNAME() that checks for the user in each of the the location_departure dimension table and location_arrival table. That works too so far. 

The issue becomes how that RLS is filtering the fact table. I need users to have access to records in the fact table when the user's location value matches either location_departure OR location_arrival. Here's what I'd like a user to have access to:

But the way it's working now, it's applying both filters to the fact table so that the only records available to the user are those where both the location_departure and location_arrival values are the same:

I understand what's happening, but I can't figure out a way around this issue. Any help or thoughts on a different approach?

Thank you for any insights. 

[v-jianpeng-msft]

Thank you for your solution AMeyersen and lbendlin 

Hi, @jmunn 

I wish you all the best. Super user and AMeyersen from the previous forum have provided solutions to help you solve the problem. I see that AMeyersen provides detailed screenshots and DAX expressions, I would like to confirm, have you successfully solved this issue or do you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, you can ask them in a new post and we are more than happy to continue to help you.

Best Regards

Jianpeng Li

[jmunn]

Hi @v-jianpeng-msft, my apologies for not replying sooner.

I am currently attempting the ideas that @AMeyersen and @lbendlin suggested, so not sure if I need further help or not.  I will reply to each directly. 

[AMeyersen]

Hi @jmunn ,

in the usual approach to set up a star schema, the fact table would not filter the dimension tables.
You can test using a bidrectional relationship betwenn the fact table and the two dimension tables. Make sure to check "enable security filter in both directions" in the relationship settings.

Then users would only see dimension values which are relevant for them. I am not sure about performance penalties in this approach. That's something you'd need to test.

[AMeyersen]

Hi @jmunn ,
if RLS does not need to apply to dimensions (users may see all dimension values) you could use a much simpler approach.

1. Disconnect the RLS table from both dimension tables
   
   
   
2. Update the RLS statement using the advanced editor and filter directly on the fact table
   
   
   
3. use a statement like this for RLS:

OR (
    [Location_Arrival]
        == LOOKUPVALUE ( 'RLS-Table'[Location], 'RLS-Table'[Email], USERPRINCIPALNAME () ),
    [Location_Departure]
        == LOOKUPVALUE ( 'RLS-Table'[Location], 'RLS-Table'[Email], USERPRINCIPALNAME () )
)​

If multiple locations can be assigned to single users, the DAX statement would look something like this:

VAR _locations =
    SELECTCOLUMNS (
        CALCULATETABLE ( 'RLS-Table', 'RLS-Table'[Email] = USERPRINCIPALNAME () ),
        [Location]
    )
RETURN
    OR ( [Location_Arrival] IN _locations, [Location_Departure] IN _locations )

[jmunn]

Thank you @AMeyersen!

Re: whether users may see all dimension values or not: there are ~1700 dimension values in total, so it would be somewhat cumbersome for a user to have to navigate among them, even with search enabled in the slicer. However, if I understand your suggested approach correctly, the now-RLS-filtered fact table should be able to filter the two dimension tables anyway? 

I had another question regarding this solution re: applying RLS to the fact table directly: There's a possibility that my data model will eventually grow to include additional fact tables. Would this solution be extendable to those fact tables also, or would they need to filter via relationship to the current fact table?

[v-jianpeng-msft · Accepted solution]

Hi, @jmunn 

Based on the image you showed at the end, I've created a simple data set:

My relationship is as follows:

I created an RLS role as follows:

FACT Table DAX:

VAR _user_location = MAXX(FILTER('UserInfo','UserInfo'[Email] = USERPRINCIPALNAME()),'UserInfo'[Location])
VAR _table = SELECTCOLUMNS(CALCULATETABLE('REF',FILTER('REF','REF'[Location] = _user_location)),'REF'[DepartmentArrivalCombination])
RETURN 'FACT'[DepartmentArrivalConcatenated] IN _table

Be able to get the results you expect:

I've uploaded the PBIX file I used this time below.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

I need users to have access to records in the fact table when the user's location value matches either location_departure OR location_arrival.

Create a reference table that lists all combinations in your fact table for departure and arrival.

Create another reference table with all locations.

Control the locations table from the RLS.

Control the combinations table from the locations table

Control the fact table from the combinations table.

[jmunn]

Thank you @lbendlin for this.

I'm trying to figure out how that would work: 

In this approach, it seems that for each location, the Combinations table needs to include a record for every combination in which that location is involved, departure and/or arrival. 

Is this what you had in mind?

[lbendlin]

Please provide sample data that fully covers your issue.