Row Level Security - table of multiple values was supplied where a single value was expected

[rsanyoto]

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?

[v-yiruan-msft]

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

[rsanyoto]

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?

[v-yiruan-msft]

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

[rsanyoto · Accepted 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 () )