RLS security based on State and Company table

[ashishg]

Requirement - Apply RLS

1. Fact table should be filer base on email address
2. one user could be assign to multiple state but same user can be assign withing only one company of any one particular state 

What DAX i will write to filter data
NOTE- IF user have an access of two state like MH and MP , but its having an only one company access "google" then he can be only show data of google company not for other company from another state

@amitchandak @Greg_Deckler 

[ashishg]

@v-shex-msft 

here is the tables , I'm not able to attached pbix file, Relationship as shown in above image

ReportFact

ProductID	State	Price	Company
1	MH	20	Google
2	MH	20	Google
3	MH	10	Google
4	MH	30

2. CompanyRLS

Email	Company
abc@gmail.com	Google
pqr@gmail.com	amazon

3. StateRLS

Email	State
abc@gmail.com	MH
pqr@gmail.com	KL
abc@gmail.com	MP

[v-shex-msft · Accepted solution]

HI @ashishg,

Did you mean the company RLS table is the role settings you want to filter on these tables? Is there any index fields include in your ‘State RLS’ table?

I'd like to suggest you can use the current username and index field to find out the first ‘State’. Then you can use the ‘State’ as condition to lookup the ‘report fact’ table by ‘State’ and ‘product ID’ to find out first company.

After these steps, you can extract the result 'company' and current 'username' to apply RLS filter on table feilds.

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @ashishg ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng