Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional RLS DAX

I read dozens of articles about RLS but it seems that none of them works for my case. I am sure that the solution should be simple, I just can’t find it somehow…
 
I have a table ‘Tariff’ (with thousands of rows) that shows tariffs, and a table ‘User’ (hundreds of rows) that shows which tariffs a user should see via his email.
 
The trick is that ‘Tariff’ table contains 2 types of tariffs: general tariffs (ConsignorId = «00000») and personal tariffs (ConsignorId = «12345»; «67890»; etc.).
 
When a user logs in, he should see both general and his personal Tariffs.
For example:
user [email protected] should see TariffIds: A, C, B.
user [email protected] should see TariffIds: A, C, D.
User_Tariff_tablesTables_content
So, I tried the following RLS rule, but it returns an error:
manage_roles_usermanage_roles_tariffrls_view_error
Please, help me out — what should I do? How can I solve this?
 
I also tried another way: I split the table ‘Tariff’ into 2 tables — general tariffs and personal tariffs, applied RLS on ‘personal tariffs’ table, then created a new table with UNION (‘general tariffs’, ‘personal tariffs’) but RLS did not work at all — all the rows are displayed to each user.
 
p.s. I will attach pbix file in a reply, if needed.
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    Two ways.

     

    1 add one more row to user table.

    Then RLS filter DAX:

    User:
[UserEmail] = username() || [UserEmail] = "all"

TARIFF:
[ConsignorID] = "00000"
||
[ConsignorID] in values('User'[ConsignorID])

     

    Result:

     

    2 Detele the relationship between these two tables and RLS Filter DAX:

     

    User:
[UserEmail] = username()

TARIFF:
[ConsignorID] = "00000"
||
[ConsignorID] in calculatetable(values('User'[ConsignorID]),filter('User',[UserEmail] = username()))

     

    Result:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

     

3 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Two ways.

     

    1 add one more row to user table.

    Then RLS filter DAX:

    User:
[UserEmail] = username() || [UserEmail] = "all"

TARIFF:
[ConsignorID] = "00000"
||
[ConsignorID] in values('User'[ConsignorID])

     

    Result:

     

    2 Detele the relationship between these two tables and RLS Filter DAX:

     

    User:
[UserEmail] = username()

TARIFF:
[ConsignorID] = "00000"
||
[ConsignorID] in calculatetable(values('User'[ConsignorID]),filter('User',[UserEmail] = username()))

     

    Result:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thanks a lot! it works!!! what a relief!

  • ribisht17's avatar
    ribisht17
    Super User
    4 years ago

    Anonymous 

     

    In your second DAX instead of writing false you can give a text , may be 1 or 0 and filter it out via FILTER

     

    Regards,

    Ritesh