row level security - Based on User logged in, show all the records belonging to his/her region(Geo)

[Anonymous]

Hi All,

I am trying to establish some speicifc RLS like below:

Based on the User logged in (who opens the Report Server URL), I need to filter the visuals to show all the records of Region that user belongs to.

Example, in my below screen shots, I have two table visuals built on Adventure Workd DW - Dim Customer, Dim Geograpy and FactInternetSales.

Now when a user (Aaron Kumar, second in the screen shot) opens this report, he should be able to see all the records belonging to Regoin = United States. If user (Aaron King, first in the screen shot) opens this report, he should be able to see all the records belonging to Region = Canada.

I have tried to add a Role like shown below, two rules for one role. But really dont understand how to achieve this.

Any one can suggest how to acheive this?

Thanks

Manoj

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

You could try the expression like this

[Region] in CALCULATETABLE(VALUES(Dim[Region]),FILTER(Dim,Dim[Name]=USERNAME()))

and in your report, adjust as below:

[Region] in CALCULATETABLE(VALUES(Dim[Region]),FILTER(Dim,Dim[EmailAddress]=USERNAME()))

Result:

For example:

and here is my pbix file, please try it.

Best Regards,

Lin