How to apply RLS to other dimensions when using the static RLS approach?

Question

I have 4 tables. Sales, Customers, Products, Brand. The data model is a standard star schema design.

There are 3 Customers in the Customers table, for example: Walmart, Starbucks, Kellogs.

Customer's users should be able to see respective customer data only.

For this I have created 3 roles, each role uses the static RLS that applies DAX filter on the customer table. For example: Walmart Role has following filter on Customer table: =Customers['Name']=="Walmart"

In the Power BI web service I have added relevant users into this role. When testing this I'm realising that although the Customer table is filtered correctly, and therefore the Sales table is filtered correctly, however, the problem is that the Products and Brands tables show the data belonging to all Customers.

When user belonging to Walmart role uses the report, then I want them to be able to see the Products and Brands of Walmart only.

How to achieve this in Power BI?

I tried to set DAX (RLS) on the Brand table, for example: =Brand['ID'] IN VALUES(Sales['BrandID'])

But this doesn't give me the expected result. It still shows all the Brands.

Update: An answer suggested using visual level filter on the Product and Brand slicers (COUNTROWS(SALES)>0), however this is somewhat like a report level solution. If I follow this approach, then my end users having build permission on the dataset can see all Products and Brands when building new reports. So I'm looking for a dataset level (RLS) solution.

Consider your data model is terms of security. If assuming a Product and Brand can belong to multiple customers, then the suggested answer is correct, there is no security on Product and Brand and instead this becomes a visual preference. If however there are Product and Brand per customer then you can approach this with RLS if there is Customer ID column in Product and/or Brand. — Sam Nseir
– Sam Nseir, Commented Jul 2, 2024 at 0:14
I have some products that we sell across customers. There is no customer ID column in product/brand table. If I add customer ID to product/brand table, then the relation between this and the fact table will be M:M — variable
– variable, Commented Jul 2, 2024 at 2:57
Understood - so with that, the proposed answer is the way to go. A "security" need for Brand and Product isn't there or needed. These two are dimension tables and not related to Customer. Most cube consumers should understand that and they will see all in these dimension tables until a Fact measure is added (and then the Customer RLS comes to play). — Sam Nseir
– Sam Nseir, Commented Jul 2, 2024 at 4:41
The proposed answer is a visual level filter. My customer who has build permission on dataset can see other customers products in the slicer when building new reports. This is not acceptable. Basically I need security at the product/brand dimensions also. Is there any solution to this? — variable
– variable, Commented Jul 2, 2024 at 5:07
I humbly disagree with your statement. A report builder will see ALL Product & ALL Brand, they will not know or see which Customer has what of those. I'll post an answer if you really want to go down that path. — Sam Nseir
– Sam Nseir, Commented Jul 2, 2024 at 7:40

Sam Nseir · Accepted Answer · 2024-07-06 02:42:18Z

2

+100

At RLS execution time, no other RLS is executed, so you will need to apply the same rule condition for the other dimension tables.

For example, for your Products table, add this rule:

var pIDs = 
  CALCULATETABLE(
    DISTINCT('Sales'[Product ID]),
    Customers[Name] = "Walmart"
  )
var fltr = FILTER('Products', [ID] IN pIDs)

RETURN COUNTROWS(fltr) > 0

Concise version:

[ID] IN 
  CALCULATETABLE(
    DISTINCT('Sales'[Product ID]),
    Customers[Name] = "Walmart"
  )

Not ideal as now you will need to maintain the same RLS logic for each table rule:
Customers[Name] = "Walmart"

edited Jul 6, 2024 at 2:42

answered Jul 2, 2024 at 7:49

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

variable Over a year ago

Why isn't RLS on product table like the following not working? =Product['ID'] IN VALUES(Sales['ProductID'])

Sam Nseir Over a year ago

As mentioned above, at RLS execution time, no other RLS rules are in effect.

variable Over a year ago

How about something like this then: =Product['ID'] IN CALCULATETABLE(VALUES(Sales['ProductID']),Customer['Name']='Walmart')

Sam Nseir Over a year ago

Yes, that should work - more concise version of the above answer.

variable Over a year ago

Please update this version in your answer as well for simplicity.

|

Collectives™ on Stack Overflow

How to apply RLS to other dimensions when using the static RLS approach?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related