Forum Discussion
Pass measure to SQL Query in Power BI
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.