Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Back using Power BI after almost 1 year away and I need help setting up Row Level Security!
Our datasets consists of SFDC objects (Opportunities, Tasks, Users) which are refreshed periodically every day and I am wanting to use RLS so that when a user logs in to a given dashboard in Power BI service they will only see their own data. The basic filtering would look something like this:
User can only see records from Tasks and Opportunities in which they are the CreatedBy.
Example: User Joe Smith (id 05Q00000aff02xb) logs into Power BI and goes to the Opportunity Dashboard. He will only see Opportunities where the CreatedById = (05Q00000aff02xb)
I've done this before but am having to relearn everything and I'm not sure where to start!
Any help is greatly appreciated...
Mike
Solved! Go to Solution.
Hi @megachuckmc721,
You need to build relationships between these tables and modify the crossfitlerdirection option to both to confirm user table can effect all of them.
Then use user table to create RLS, if you correctly setting these relationships, it will effect on all related tables.
Power BI – Dynamic Row Level Security – Tips to get it working!
Regards,
Xiaoxin Sheng
Hi @megachuckmc721,
You can refer to following blog to know more about how to use RLS with USERNAME function.
Power BI Desktop Dynamic security cheat sheet
BTW, if you table not contains mapping table(domain/userid to table userid), you need to create one and add to your datasource. Then modify the 'cross filter direction' option to both and turn on the 'Apply security filter in both directions property'
Regards,
Xiaoxin Sheng
Hi @megachuckmc721,
You need to build relationships between these tables and modify the crossfitlerdirection option to both to confirm user table can effect all of them.
Then use user table to create RLS, if you correctly setting these relationships, it will effect on all related tables.
Power BI – Dynamic Row Level Security – Tips to get it working!
Regards,
Xiaoxin Sheng
Thanks for your help on this - I'm good to go now, although I went ahead and set up separate RLS Roles for each individual. Works fo now but hopefully I can figure out how to use the Username() functionality and cut down on the duplication of roles.
Thanks!
Mike
Hi @megachuckmc721,
You can refer to following blog to know more about how to use RLS with USERNAME function.
Power BI Desktop Dynamic security cheat sheet
BTW, if you table not contains mapping table(domain/userid to table userid), you need to create one and add to your datasource. Then modify the 'cross filter direction' option to both and turn on the 'Apply security filter in both directions property'
Regards,
Xiaoxin Sheng
Thanks! I will take a look at the cheatsheet with regards to using Username() and RLS!
And good reminder on the domain/userid mapping as well!
Mike
I'm not sure I fully understand your explanation...
I have 3 tables with the following relationships: One one of the relationships I can set the crossfilter direction as both but get an error on the other(?) - Also your explanation suggests that for each user I will have to create a Role that filters on the UserID - which means 1 ROle for each user. I read somewhere about using Username() - Is that correct.
Thanks again for any help on this.
Mike
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |