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.
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"