Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
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.
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.
Solved! Go to Solution.
Kudos for a superbly formulated post
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
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.
Kudos for a superbly formulated post
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |