RLS for multiple views

[ameldrum]

Hello,

I am stuggling with relationships and RLS. His is some sample data that illustrates my set up. 

So, I want to be able to filter the Fact_Table by Zone_List on Zone_dim where it includes ALL Appt IDs where EITHER client OR provider is in said zone.  Right now, if I filter to EAST, I get Appt ID 1 and 2 where I would like to get 1,2,3.  The reason for this is RLS.  With RLS I want to be able to assign some viewers zone level view that will show them stats from Fact_Table about both providers and clients in the assigned zone.  I also want to assign RLS to providers so that they only see data from the Fact_Table that aligns with them.  

Things I have tried:

I tried using USERELATIONSHIP but the issue is that with RLS on Zone_Dim it filters Provider_Dim then Fact_table to only zones where the provider is in so like the example above, Appt_ID 3 is filtered out and not avaiblible in any calculations.

I tried created bridge tables, Zone_Dim_RLS where it combines the Client and Provider IDs and all the zones they are associated with into one table, and Fact_Table_RLS which unpivots the Client and Provider IDs on the Appt ID so there is one column of Person_ID and an identifying column Client_or_Provider.  This way, if RLS is on Zone_Dim, it will give a list of all Clients and Providers in that zone, pass that list to the Fact table via the Fact_Table_RLS and produce all the Appts IDs with the selected zone as shown in this diagram:

This works perfectly until I try to add other Fact tables that need to be filtered with the same RLS on Zone_Dim through the Provider_Dim table. (Zone_Dim -> Provider_Dim -> Other_Fact_Table)

I tried making the relationship between Zone_Dim_RLS and Provider_Dim which solves the issue of RLS on Zone_Dim -> Provider_Dim but then behaves strangely  on the Fact_Table. Provider details do not show up when looking at Clients in a zone.

In the end, I would like either a DAX measure or new scema that will allow RLS to be applied at the Zone_Dim level to filter the Fact_Table to include ALL clients AND providers in that zone while also being able to filter all providers in that zone to filter other fact tables by zone.  

Thank you in advance!

[ameldrum · Accepted solution]

I ended up going with a version of my second example.

With the connection between Provider_Dim and Zone_Dim_RLS going both ways, I can properly filter Fact_Table by either Zone from Zone_Dim or Provider ID from Provider_Dim.  It did cause some missing data on the provider side (blanks on the screen shot above) but I solved this with help from the post from Pragati and the inactive relationship between Provider_Dim and Fact_Table

SelectedProviderName = 
CALCULATE(
    MAX(Provider_Dim[Provider ID]),
    USERELATIONSHIP(Fact_Table[Provider ID], Provider_Dim[Provider ID])
)

This was a "New Column" measure to bring in the missing provider info, which, once added to the table made it look perfect.This solution also allowed for the proper filtering of Fact_Table_2 which, included providers not listed in the Fact_Table, by either Zone_Dim or Provider_Dim.  

The Fact_Table_RLS is actually a nice feature as well because my Fact table has like 20 metrics that need to be seen at both the Provider and Client levels. The provider ones always just worked as is because the data was filtered by provider, I created measures using USERELATIONSHIP to get all the metrics for Client view, however, RLS prevented this from working properly as many clients were being left out (which prompted this post).  Now that I have Fact_Table_RLS, not only is all the data available that I need, but now I have a simple switch I can activate to see things from either Provider OR Client view.I no longer need all of those measures because the calc will work just need to switch the view I want.

Thanks for the replies and help.  Hopefully others will find this useful.

[v-linhuizh-msft]

Thanks for the reply from Pragati11.

Hi @ameldrum ,

Based on your description I created a new table:

Add a column to Zone_Dim table:

Change the relationships to look like the one shown below:

Create new security roles:

VAR _zone = SELECTCOLUMNS(FILTER('Zone_Dim', 'Zone_Dim'[Email] = USERPRINCIPALNAME()), "ZoneList", 'Zone_Dim'[zone List])
VAR _client = SELECTCOLUMNS(FILTER('Client_Dim', 'Client_Dim'[Zone2] IN _zone), "ClientID", 'Client_Dim'[Client ID])
VAR _provider = SELECTCOLUMNS(FILTER('Provider_Dim', 'Provider_Dim'[zone1] IN _zone), "ProviderID", 'Provider_Dim'[Provider_ID])
RETURN 
    OR(
        'Fact_Table'[Cilent ID] IN _client,
        'Fact_Table'[Provider ID] IN _provider
    )

Result:

Best Regards,
Zhu

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

[ameldrum]

Zhu,

Thank you for your response.  This is a very clever way to get RLS to work in these parameters.  However, my client and provider dimention tables contain a lot of information that needs to be used in connection with this fact table as well as many other fact tables so while it will filter correctly, it wont give me the connections I need. I really appreciate your response and learned quite a bit from your solution!

[Pragati11]

Hi @ameldrum ,

can you please check if any if the references mentioned in this existing thread helps?

https://community.fabric.microsoft.com/t5/Desktop/Dynamic-Row-level-security-based-on-multiple-colum...