Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hello experts,
Situation:
For our RLS requirement we have users with e-mailadresses that need access to different departments. The departments are stored in tableC RLS. At this moment the row level security don’t work if a user are authorized for more than 1 department. The registration of this authorization are recorded in tableC RLS.
The requirement:
Filtering the Table B Master based on the table A Person. Table C RLS is needed because I am using the email address of each person. In Table A Person there is no any column that provide email address.
The current RLS Table filter DAX expression:
PATHCONTAINS('TableA Person'[Org pad],LOOKUPVALUE('TableC RLS'[directie],'TableC RLS'[E-mailadres],USERPRINCIPALNAME()))
This is a dax expression of the column Org code from TableA Person:
Org code= IF('TableA Person'[org code]=50293450,"50309859|50297771|63494746",
IF('TableA Person'[org code]=50293502,"50309859|50297771|63494746",
LOOKUPVALUE('TableB'[Org path], 'TableB Master''[CodeUnit], 'TableA Person''[Org code])))
--This is a hardcoded way of creating DAX—
This is a dax expression of the column directive from TableC RLS:
Directie = IF
(LOOKUPVALUE('TableB Master'[Nivo 3],'TableB Master'[CodeUnit],'TableC RLS'[Organisatie])=0,
(IF(LOOKUPVALUE('TableB Master'[Nivo 2],'TableB Master'[CodeUnit],'TableC RLS'[Organisatie])=0, 'TableC RLS'[Organisatie] , (LOOKUPVALUE('TableB Master'[Nivo 2],'TableB Master'[CodeUnit],'TableC RLS'[Organisatie]) ))),LOOKUPVALUE('TableB Master'[Nivo 3],'TableB Master'[CodeUnit],'TableC RLS'[Organisatie]))
The current model:
Any suggestions?
Solved! Go to Solution.
Hi @v-yiruan-msft ,
I have fixed the issue. the following dax table filter is applied in the Table B Master
var variable1 =
SUMX (
FILTER ( 'Table C RLS', 'Table C RLS'[email] = USERPRINCIPALNAME () ),
IF (
PATHCONTAINS ( 'TableB Master'[Org Path], 'Table C RLS'[Organisatie] ),
1,
0
)
)
RETURN
IF ( variable1> 0, TRUE (), FALSE () )
Hi @rsanyoto ,
Please try to update RLS table filter as below and check whether it still get the above error... If it is not working, please provide some sample data(exclude sensitive data) in Table A, Table B and Table C and the related logic of RLS.
PATHCONTAINS (
'TableA Person'[Org pad],
CALCULATE (
FIRSTNONBLANK ( 'TableC RLS'[directie], 1 ),
FILTER ( 'TableC RLS', 'TableC RLS'[E-mailadres] = USERPRINCIPALNAME () )
)
)
In addition, you can also refer the method in the following link to fix it.
Dealing with Duplicates “A Table of Multiple Values was supplied” using DAX
Best Regards
Hi @v-yiruan-msft ,
Thank you for your response.
the function FIRSTNONBLANK or LASTNONBLANK will return only 1 record. my requirement is to return at least 2 records....
Any suggestion to change the FIRSTNONBLANK into something else?
Hi @rsanyoto ,
As you mentioned in previous post, someone may have multiple department, could you please provide some original data of Table A Person,TableB Master and Table C RLS with these specific person in order to provide a suitable solution? Thank you.
Dynamic RLS - use DAX to return multiple values IN
Best Regards
Hi @v-yiruan-msft ,
I have fixed the issue. the following dax table filter is applied in the Table B Master
var variable1 =
SUMX (
FILTER ( 'Table C RLS', 'Table C RLS'[email] = USERPRINCIPALNAME () ),
IF (
PATHCONTAINS ( 'TableB Master'[Org Path], 'Table C RLS'[Organisatie] ),
1,
0
)
)
RETURN
IF ( variable1> 0, TRUE (), FALSE () )
User | Count |
---|---|
105 | |
69 | |
48 | |
47 | |
47 |