RLS with Direct Query

[Kanth]

Hello All,

We are migrating from import mode to direct query mode and would like to implement the same RLS we have in import mode. Unfortunately, our RLS uses LOOKUPVALUE function which is not supported in direct query. Here is our current RLS expression.

[V] = LOOKUPVALUE(

           'Sec'[V],

           'Sec'[User],USERPRINCIPALNAME(),

       'Sec'[V],'DIM'[V]

         )

||

[PC] = LOOKUPVALUE(

           'Sec'[PC],

           'Sec'[User],USERPRINCIPALNAME(),

       'Sec'[PC],'DIM'[PC])

Single user can have multiple V's or PC's. I have tried multiple things but it is not working as expected. Could you please help me with this?

[Anonymous]

Hi @Kanth ,

Your expression looks great, does your user have permission above view causing the RLS to not work?

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Kanth]

This expression works fine with import mode but due to some limitations LOOKUPVALUE function wont work in direct query mode.

[lbendlin]

What made you choose LOOKUPVALUE? Why not TREATAS or the data model?

[Kanth]

I have to filter based on two different columns and need to have two relationships instead of using USERELATIONSHIP and all I opted LOOKUPVALUE. I am not faimilar with TREATAS function. Does it work in direct query? If so, could you please let me know how can I achieve my requirement

[lbendlin]

Yes, TREATAS will work.

Propagating filters using TREATAS in DAX - SQLBI

[Kanth]

Looks like TREATAS function is also not allowed in direct query mode.

[lbendlin]

Looks like TREATAS function is also not allowed in direct query mode.

Do you have a link to a document that states that?

[aj1973]

Hi @Kanth 

Try

Lookup Measure = 

CALCULATE(

    MAX('Table'[ResultColumn]),

    FILTER(

        ALL('Table'),

        'Table'[SearchColumn] = SELECTEDVALUE('CurrentTable'[SearchColumn])

  )

)

[Kanth]

This will only work when I have only one value for a user. But, I have more than one value for a single user.