Multi Criteria or Tables for RLS

[Anonymous]

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

Power BI file

https://www.dropbox.com/s/q3zvi12nwmprq4q/Hierarchy%20RLS-demo%20v2%20%281%29.pbix?dl=0 

Data File

https://www.dropbox.com/s/8dl3mvkfyiv43qe/ML%20RLS%20data%20v2.xlsx?dl=0 

[v-joesh-msft]

Hi @Anonymous ,

Try the following:
1. Create a new table Region / County / SP, which is equivalent to groupby Sales Table according to the Region, Country, Sales Person fields, and create the unique field "custom"

2. Fill the unique field in the table Region / County / SP into the row of the table User Right (Step 1 Match Region) "All" and create the unique field "custom"
3. Create the relationship between the table Region / County / SP and the table User Right (Step 1 Match Region) with the custom column
4. Re-managing roles

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EcC_CsNcblFBiapts2L0wgsBI0s3lohHv8vKkmyqXHXFPQ?e=79NVla

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks. I may try for it.

I upload my model and all sensitive data has been masked and I hope it can give you better idea.

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.

Power BI file

https://www.dropbox.com/s/cats3zhke3kvk1a/Demo.pbix?dl=0 

Fact Table

https://www.dropbox.com/s/zedoht4tqj7155r/EU-DATA-FACT.xlsx?dl=0 

Dim Table

https://www.dropbox.com/s/83diyxhxfzuen4b/EU-DATA-DIM.xlsx?dl=0 

Control Table

https://www.dropbox.com/s/edyqumhvebngd7z/RLS%20control-add%20sales%20person.xlsx?dl=0