cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aabi Frequent Visitor
Frequent Visitor

Question about Row Level Security - Viewing permission from 1 table

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

 

RegionSalesUnitsOther Columns
America10010x
Europe20020xx
Asia30030xxx
MENA40040xxxx


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

[Region ] IN {"America"}

RegionSalesUnitsOther
America10010x

and if I want to add him also to see Europe 

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

RegionSalesUnitsOther
America10010x
Europe20020xx

 

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

 

RegionSalesUnitsOther
America10010x
Europe 20xx
Asia 30xxx
MENA 40xxxx

 

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!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Question about Row Level Security - Viewing permission from 1 table

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

 

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

Re: Question about Row Level Security - Viewing permission from 1 table

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!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,372)