Forum Discussion

ameldrum's avatar
ameldrum
Frequent Visitor
1 year ago
Solved

RLS for multiple views

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 ...
Need Help
  • ameldrum's avatar
    ameldrum
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
ameldrum
Frequent Visitor
1 year ago

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!