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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdbuchanan71
Super User
Super User

Row level security and bidirectional cross-filtering

I am struggling with row level security and bidirectional cross-filtering (I know, shocking).

I have filtering working on everything except vMEMBER which sits at the top of a relationship.

Model.jpg

vSECURITY is a table holding users and what client / groups they can see. Some users can see all the groups in a client and some can see only certain groups in a client. Further, some users can see groups under multiple clients.

Roles are set at the client level with two filters.

  1. The Client ID is applied to the entire model.
  2. Users are only allowed to see groups they cover or all groups for a client. If the entry in vSECURITY[Group ID] = "AllGroups" the user can see all groups under that client.

Here is the RLS filter applied to the vGROUP table

VAR _UPN = USERPRINCIPALNAME()
VAR _GroupList =
    CALCULATETABLE ( 
            SUMMARIZE(vSECURITY,vSECURITY[Client ID],vSECURITY[Group ID]),
            FILTER ( vSECURITY, vSECURITY[User_Email] = _UPN ) 
        )
VAR _AllGroupList =
    CALCULATETABLE(
        VALUES ( vSECURITY[Client ID] ),
        FILTER ( vSECURITY, vSECURITY[User_Email] = _UPN && vSECURITY[Group ID] = "AllGroups")
    )
RETURN
    CALCULATE(
        COUNTROWS( vGROUP ),
        INTERSECT(SUMMARIZE(vGROUP,vGROUP[Client ID],vGROUP[Group ID]),_GroupList)
    )
    +
    CALCULATE(
        COUNTROWS( vGROUP ),
        INTERSECT(SUMMARIZE(vGROUP,vGROUP[Client ID]),_AllGroupList)
    )
    > 0

In the model Groups flow down to Plans to Enrollment. Plans have a group ID and when a member is enrolled in a plan you can determine the Group ID that member is associated with. The above security filter on vGROUP works fine.

The problem is the master vMEMBER table. This table has all members and is linked to vENROLLMENT using bidirectional cross-filtering. If I enable "Apply security filter in both directions" the model works until a user is a assigned to more than one role.

Single RoleSingle RoleMulti RoleMulti Role

I am looking for a way to apply row level security filtering to vMEMBER based on the information in vSECURITY that does not require "Apply security filter in both directions" to be enabled so users can be members in more than one Role.

My sample file is attached.

2 ACCEPTED SOLUTIONS
sturlaws
Resident Rockstar
Resident Rockstar

Kudos for a superbly formulated post Smiley Happy

 

I have found one work-around for this

Member Member Count =
CALCULATE (
    DISTINCTCOUNT ( vMEMBER[Client_Subscriber_Member_ID] );
    FILTER (
        vMEMBER;
        vMEMBER[Client_Subscriber_Member_ID]
            IN VALUES ( vENROLLMENT[Client_Subscriber_Member_ID] )
    )
)

I have not tested the performance of this measure, but on this dataset it seems reasonable. The measure can also be added to the filter of the table visual and set to [Member member count]=1 to show only the corresponding [Client_subscriber_member_id].  Based on your report, this will allow you unchech the 'Apply security filter in both directions'.

View solution in original post

Thank you for the reply @sturlaws ,

The challenge with that measure is, because the RLS is not driving up to the vMEMEBER the count is not getting limited.  It's sort of an odd behavior but seems to bee the case. 

You did make me realize that the same logic I use on the group filter could be used to limit the vENROLLMENT then a count of the filtered vENROLLMENT (filtered in the role measure, not from model relationships) would work.  I will have to test the efficiency though.  vENROLLMENT is 3M lines and vMEMEBER is 1.5M

So this is the RLS DAX expression applied to vMEMEBER.

 

VAR _UPN = USERPRINCIPALNAME()
VAR _GroupList =
    CALCULATETABLE ( 
            SUMMARIZE(vSECURITY,vSECURITY[Client ID],vSECURITY[Group ID]),
            FILTER ( vSECURITY, vSECURITY[User_Email] = _UPN ) 
        )
VAR _AllGroupList =
    CALCULATETABLE(
        VALUES ( vSECURITY[Client ID] ),
        FILTER ( vSECURITY, vSECURITY[User_Email] = _UPN && vSECURITY[Group ID] = "AllGroups")
    )
RETURN
    CALCULATE(
        COUNTROWS( vENROLLMENT ),
        INTERSECT( SUMMARIZE ( vENROLLMENT , vENROLLMENT[Client ID] , vENROLLMENT[Group ID] ) ,_GroupList )
    )
    +
    CALCULATE(
        COUNTROWS( vENROLLMENT ),
        INTERSECT( SUMMARIZE ( vENROLLMENT , vENROLLMENT[Client ID] ) , _AllGroupList )
    )
    > 0

 

RLSFilteringWorking.jpg

 

As to your second question, the hard coding on the vCLIENT is mainly out of a desire to be certain on the security filtering because of not 100% trusting the measure filtering in Roles.

I had the vCLIENT filtering dynamic previously and if I do that and rely on only 1 security role then I don't have an issue because I can enable RLS on the bidirectional cross-filtering relationship.

This is probably the route I will end up going but I was hopeful the other solution could be made to work.

My updated file with the new Role filter on vMEMBER is attached.

View solution in original post

3 REPLIES 3
sturlaws
Resident Rockstar
Resident Rockstar

Kudos for a superbly formulated post Smiley Happy

 

I have found one work-around for this

Member Member Count =
CALCULATE (
    DISTINCTCOUNT ( vMEMBER[Client_Subscriber_Member_ID] );
    FILTER (
        vMEMBER;
        vMEMBER[Client_Subscriber_Member_ID]
            IN VALUES ( vENROLLMENT[Client_Subscriber_Member_ID] )
    )
)

I have not tested the performance of this measure, but on this dataset it seems reasonable. The measure can also be added to the filter of the table visual and set to [Member member count]=1 to show only the corresponding [Client_subscriber_member_id].  Based on your report, this will allow you unchech the 'Apply security filter in both directions'.

Thank you for the reply @sturlaws ,

The challenge with that measure is, because the RLS is not driving up to the vMEMEBER the count is not getting limited.  It's sort of an odd behavior but seems to bee the case. 

You did make me realize that the same logic I use on the group filter could be used to limit the vENROLLMENT then a count of the filtered vENROLLMENT (filtered in the role measure, not from model relationships) would work.  I will have to test the efficiency though.  vENROLLMENT is 3M lines and vMEMEBER is 1.5M

So this is the RLS DAX expression applied to vMEMEBER.

 

VAR _UPN = USERPRINCIPALNAME()
VAR _GroupList =
    CALCULATETABLE ( 
            SUMMARIZE(vSECURITY,vSECURITY[Client ID],vSECURITY[Group ID]),
            FILTER ( vSECURITY, vSECURITY[User_Email] = _UPN ) 
        )
VAR _AllGroupList =
    CALCULATETABLE(
        VALUES ( vSECURITY[Client ID] ),
        FILTER ( vSECURITY, vSECURITY[User_Email] = _UPN && vSECURITY[Group ID] = "AllGroups")
    )
RETURN
    CALCULATE(
        COUNTROWS( vENROLLMENT ),
        INTERSECT( SUMMARIZE ( vENROLLMENT , vENROLLMENT[Client ID] , vENROLLMENT[Group ID] ) ,_GroupList )
    )
    +
    CALCULATE(
        COUNTROWS( vENROLLMENT ),
        INTERSECT( SUMMARIZE ( vENROLLMENT , vENROLLMENT[Client ID] ) , _AllGroupList )
    )
    > 0

 

RLSFilteringWorking.jpg

 

As to your second question, the hard coding on the vCLIENT is mainly out of a desire to be certain on the security filtering because of not 100% trusting the measure filtering in Roles.

I had the vCLIENT filtering dynamic previously and if I do that and rely on only 1 security role then I don't have an issue because I can enable RLS on the bidirectional cross-filtering relationship.

This is probably the route I will end up going but I was hopeful the other solution could be made to work.

My updated file with the new Role filter on vMEMBER is attached.

The 3 roles are the same exact for the hard coding of in the filter of vClient, is it not? Is it a requirement that there should be separate roles for each [Client ID]? If not, it could be made dynamic by something like this

vCLIENT[Client ID]
    IN CALCULATEtable (
        VALUES ( vSecurity[Client ID] ),
        FILTER ( vSecurity, vSecurity[User_Email] = USERPRINCIPALNAME () )
    )

and you would only need 1 role.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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