Multi Criteria or Tables for RLS

[chiu2003]

Hi all,

Thanks for colleague assistance, I can handle multi layer RLS (Region, then country).

However, I get a new requirement and need to control access for Sales Person. (add a new criteria)

Below is my model:

1. Sales Table (now can filter by region/country, but not sales person)
2. User Right table for RLS (identify user can see what data)
3. Region/Country table (just for RLS for Region/country)
4. Sales Person table (can be modify)

I have already setup role to control region/country, but I have no idea how to add the sales person criteria,

I can limit Boris can view Germany data only, but I would like to limit Boris can only view Germany AND SP-1 Sales data.

Do I need to implement a complex DAX or modify the model?

I have uploaded my mock up file. Thanks

ML RLS data v2.xlsx (16.2 KB) Hierarchy RLS-demo v2 (1).pbix (101.2 KB)

[Melissa]

Hi @chiu2003,

I have some concerns about the way your model is developing. Let me try to clarify, RLS will be applied on your User Rights table so I moved that to the top and placed your dimension/lookup tables below that. Now you can clearly see that the filter direction runs in the opposite direction of what is considered desired.

So I denormalized your User Rights table so it now holds all dimensions for Region, Country and Sales Person. First I created a List that holds the sales person code for each row (see below) and then chose the option “expanded to new rows”

Split by delimiter the RC Permission column so we have a valid value for Region & Country on each row and concateneted a new RLS key containing these 3 dimensions.

Created the same RLS key in your sales table and cleaned up some columns here and there, reordered the model so this is what it looks like now, all filters are moving in the right direction.

Give it a go, I hope this solves it for you.
Hierarchy RLS-demo v3.pbix (110.8 KB)

[chiu2003]

Hi Melissa,

Thanks. It already solve 90% of the problem.

I apply you recommendation in my model and it can limit the data access for each user .

Actual, there is no relationship between region and sales person and they came from 2 different data sources, but i may create a table for all combination of region and sales person.

But how can I keep to use DIM-Customer as filter table (slicers in report) as it get more customer information that exclude in User control table?

As I cannot build active relationship between User Control table and DIM-Customers table, and user can see all RSO value in the slicer even though they cannot view the data
(Steven should able to see DACH, NL, PL only).

I would like the user login to Power BI and review their own region data without any further selection.

I upload my model and all sensitive data has been corrected. Thanks.

Demo.pbix (1.2 MB)
EU-DATA-DIM.xlsx (198.3 KB) EU-DATA-FACT.xlsx (1.7 MB)RLS control-add sales person.xlsx (18.2 KB)

[Melissa]

@chiu2003

Actual, there is no relationship between region and sales person and they came from 2 different data sources, but i may create a table for all combination of region and sales person.

But how can I keep to use DIM-Customer as filter table (slicers in report) as it get more customer information that exclude in User control table?

@chiu2003 once you’ve established a commonality between DIM Cstomer and User control in your model you could use a measure to filter Slicers in your report, here’s an example.

Hopefully this will get you over the finish line

Power BI One Slicer to filter other slicers based on Items that has value only without BI Direction

[sam.mckay]

Hi @chiu2003, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!