RLS Issue

[TaroGulati]

Hi all, 

I am having difficulty in implmenting the following scenario in RLS:

from the fact table user should see all office code for customer code 589 and all customer code for office code 435.

is it possible to implement this cross data role?

here is the link of PBIX: RLS Test.pbix

Thanks

[Sandip_Palit · Accepted solution]

Of course. This scenario is a classic "OR" condition in Row-Level Security (RLS) and is straightforward to implement by applying a filter directly to your Fact table.

The key is to create a single DAX filter expression on the Fact table that checks for either condition being true for a row to be visible.

Step-by-Step Solution
Here’s how to set up the RLS rule in your Power BI file.

1. Create a New Security Role
In Power BI Desktop, go to the Modeling tab on the ribbon and click Manage roles.

In the "Manage roles" window, click Create to make a new role. You can name it something descriptive, like Sales Cross Filter.

2. Apply the DAX Filter to the Fact Table
After creating the role, select your Fact table from the list of tables.

In the Table filter DAX expression box, enter the following formula. This expression tells Power BI to show a row if the customer code is 589 OR if the office code is 435.

[Customer Code] = 589 || [Office Code] = 435
Click the checkmark to validate the expression, and then click Save.

How to Test Your RLS Rule
Before publishing, you should always test your role to ensure it works correctly.

From the Modeling tab, click View as.

In the "View as roles" window, check the box for the new role you created (Sales Cross Filter).

Click OK.

If this explanation and solution resolve your issue, please like and accept the solution.

[v-sdhruv]

Hi @TaroGulati ,

Just wanted to check if you were able to resolve the issue?

[Sandip_Palit · Accepted solution]

Of course. This scenario is a classic "OR" condition in Row-Level Security (RLS) and is straightforward to implement by applying a filter directly to your Fact table.

The key is to create a single DAX filter expression on the Fact table that checks for either condition being true for a row to be visible.

Step-by-Step Solution
Here’s how to set up the RLS rule in your Power BI file.

1. Create a New Security Role
In Power BI Desktop, go to the Modeling tab on the ribbon and click Manage roles.

In the "Manage roles" window, click Create to make a new role. You can name it something descriptive, like Sales Cross Filter.

2. Apply the DAX Filter to the Fact Table
After creating the role, select your Fact table from the list of tables.

In the Table filter DAX expression box, enter the following formula. This expression tells Power BI to show a row if the customer code is 589 OR if the office code is 435.

[Customer Code] = 589 || [Office Code] = 435
Click the checkmark to validate the expression, and then click Save.

How to Test Your RLS Rule
Before publishing, you should always test your role to ensure it works correctly.

From the Modeling tab, click View as.

In the "View as roles" window, check the box for the new role you created (Sales Cross Filter).

Click OK.

If this explanation and solution resolve your issue, please like and accept the solution.

[TaroGulati]

Hi, 

thanks for the reply. 

is it possible to achieve the result by applying RLS on the dimension table instead of fact in this case?

Thanks

[FBergamaschi · Accepted solution]

Yes it is possible and is highly recommended

create two roles

1

Dim1[Customer Code] = 589 

2

Dim2[Office Code] = 435

Then include the person in both roles in the cloud (assign the person to both roles)

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI