Conditional RLS DAX

[Anonymous]

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 1@gmail.com should see TariffIds: A, C, B.

user 2@gmail.com 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 · Accepted solution]

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]

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

[ribisht17]

@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