topic Re: Conditional RLS DAX in DAX Commands and Tips

Conditional RLS DAX

Anonymous — Mon, 11 Jul 2022 15:19:36 GMT

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.

So, I tried the following RLS rule, but it returns an 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.

Re: Conditional RLS DAX

ribisht17 — Mon, 11 Jul 2022 17:07:11 GMT

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

Re: Conditional RLS DAX

v-chenwuz-msft — Thu, 14 Jul 2022 09:04:46 GMT

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.

Re: Conditional RLS DAX

Anonymous — Fri, 15 Jul 2022 11:27:16 GMT

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