Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Power BI Experts
I have the below SQL Query
select * from table1 where (field1 like (select field1 from users where (UserName=@CurrentLoginUser)) + '%')
I added the above query in Power BI, I would like to set this SQL parameter "@CurrentLoginUser" with the current user who browses the report.
If the answer is No so How can achieve that in Power BI
BTW, I have tried Manage roles that no help me in this scenario
Any help would be appreciated!
One solution we found for RLS is to bring in another table with the correct parameters( IE. Username, RegionKey). In our case, we used Username (AD Username) and then embedded the dashboard in our sharepoint site. We then used Sharepoint to pass the URL with the username. This allowed us to display the correct dashboard to the correct user.
Below is the URL we are using to filter the dashboard. Add this after the dashboard path
?rs:Embed=true&filter=TableName%2FPARAMETER%20eq%20%27USERNAME%27
So in your exact case, you'd want something like
(qualified server path)?rs:Embed=true&filter=table1%2Ffield1%20eq%20%27UserName%27
Hope this helps.
Kevin
Thank you for your reply!
If you're connecting to SQL Server, you might try using SYSTEM_USER as described here:
Thank you for your reply, I really appreciate this workaround!
But unfortunately this is not working because I can't control the data source authentication type for Power BI report, I can do this for the normal SSRS report but for Power BI I can't and I must specify username and password to can access the database during configuring "Get Data" In Power BI DeskTop, so in this case, the current user will be the same user that has been provided.
Please, If you have any workaround for this please post it!
As you note, this approach only works if you are using 'DirectConnect' and using the current credentials of the logged in user to connect to the database.
If you can't do that, the only other approach I know of is to build custom RLS in DAX. There's a brief bit of explanation on that approach in the thread I linked to. I've not done this in any serious way, but in general:
- you'll need a dedicated security table with columns for the user ID and whatever value you're filtering on
- the DAX USERNAME() function gives you the user ID of the logged on user
- you create a measure based on the user ID (see the previously linked thread for details)
- you can then filter a visual using that measure
The big drawback I see is that your RLS is based on a visual level filter, which users can change. There's likely a better way to take these basic tools (a dedicated security table the DAX USERNAME() function) to create custom RLS that goes beyond the visual level filter, but I've not dug into that.
User | Count |
---|---|
89 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |