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
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!
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
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!
User | Count |
---|---|
160 | |
111 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |