cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 1@gmail.com should see TariffIds: A, C, B.
user 2@gmail.com should see TariffIds: A, C, D.
User_Tariff_tablesUser_Tariff_tablesTables_contentTables_content
So, I tried the following RLS rule, but it returns an error:
manage_roles_usermanage_roles_usermanage_roles_tariffmanage_roles_tariffrls_view_errorrls_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.
1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @Anonymous ,

 

Two ways.

 

1 add one more row to user table.

vchenwuzmsft_0-1657789028036.png

Then RLS filter DAX:

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

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

 

Result:

vchenwuzmsft_1-1657789155396.png

 

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:

vchenwuzmsft_2-1657789376154.png

 

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.

 

 

View solution in original post

3 REPLIES 3
v-chenwuz-msft
Community Support
Community Support

Hi @Anonymous ,

 

Two ways.

 

1 add one more row to user table.

vchenwuzmsft_0-1657789028036.png

Then RLS filter DAX:

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

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

 

Result:

vchenwuzmsft_1-1657789155396.png

 

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:

vchenwuzmsft_2-1657789376154.png

 

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
Not applicable

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

ribisht17
Super User
Super User

@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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors