Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
aabi
Helper I
Helper I

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
v-lili6-msft
Community Support
Community Support

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.

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
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.