Question about Row Level Security - Viewing permission from 1 table

[aabi]

Hello All

I would appreciate some help on this... I think is very simple just I cannot think of the correct DAX Syntax...

I have a table with many fields/columns, for most of the fields I don't mind showing them to all however I want restriction on Region with Sales and/or Units

So I have the following table

Region	Sales	Units	Other Columns
America	100	10	x
Europe	200	20	xx
Asia	300	30	xxx
MENA	400	40	xxxx

If I want a sales person in US to see everything in America, this is easy I'm using

[Region ] IN {"America"}

Region	Sales	Units	Other
America	100	10	x

and if I want to add him also to see Europe 

[Region ] IN {"America","Europe"}

Region	Sales	Units	Other
America	100	10	x
Europe	200	20	xx

But can I make him see a table like this below ?

Region	Sales	Units	Other
America	100	10	x
Europe		20	xx
Asia		30	xxx
MENA		40	xxxx

So for Europe(or any other region) he can see the units but not to be able to see the sales while he is seeing the America Sales?

I hope I'm clear with my question!

Looking forward hearing for some ideas!

[v-lili6-msft]

hi, @aabi 

For your case, you don't need to use RLS in the report, that works on data model.

You just need to add the RLS conditional in the measure, then it will meet your requirement.

For example:

Create a measure instead of drag Sales field into visual directly.

Measure = CALCULATE( SUM([Sales] ), FILTER(  [Region ] IN {"America","Europe"} )

and then enable Show items with no data in Power BI

https://docs.microsoft.com/en-us/power-bi/desktop-show-items-no-data

Best Regards,

Lin

[aabi]

Hi @v-lili6-msft 

Thank you for your reply.

I'm already using measures in my report, and while using your idea will help me is not an efficient solution for me...

The reason is that my report has now 5 tabs which shows various breakdowns based on the "other columns" such as SKU's and Product Types.

I also have 16 regions (the 4 I said is just for the example). Therefore to do so I need 16*5 new measures (now I only have 3 one for 2017,2018 and 2019 YTD and 2 for growth), while is not hard to make them its a lot!

Also the 16 is the minumum as those are for Regional sales people, there are also regional managers which need to see various regions.

Also that would mean that I will need either 16 files/pbix (one for each region) or 96(16*6) tabs in 1 file. Then I cannot share the report with 96 tabs and I will need to make 16 dashboards which will have the 6 tabs.... As you understand this is a very overkill move to make!

While (in my mind) if I could do this somehow on a RLS level I would just make the report once and make the 16-20 roles that I need and share it to all! Everyone will be seeing only what they need to see and I would need to refresh only 1 report!

However I will use your idea now as I need to finish with the report for the regional sales managers!