Forum Discussion

Kanth's avatar
Kanth
Frequent Visitor
2 years ago

RLS with Direct Query

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?
Need Help
Show and Tell
Tips and Tricks

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

    • Kanth's avatar
      Kanth
      Frequent Visitor
      2 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Kanth
      Frequent Visitor
      2 years ago

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