Pass measure to SQL Query in Power BI

Question

Hello Power BI Experts&nbsp;&nbsp;I have the below SQL Query&nbsp;select * from table1 where (field1 like (select field1 from users where (UserName=@CurrentLoginUser)) + '%')&nbsp;I added the above query in Power BI, I would like to set this SQL parameter "@CurrentLoginUser" with the&nbsp;current user&nbsp;who browses the report.Is it possible to do that in Power BI?Is it possible to pass a measure with UserName() function to SQL Query?If the answer is No so How can achieve that in Power BIBTW, I have tried Manage roles that no help me in this scenarioAny help would be appreciated!

kmcallister · Answer

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

mqassas · Answer

Thank you for your reply!&nbsp;Is RLS is available for Power BI Report Server?!Also, I don't think it's double to add the username in the URL, I need to automatically detect the current user!&nbsp;

davidm5 · Answer

If you're connecting to SQL Server, you might try using SYSTEM_USER as described here:&nbsp;https://community.powerbi.com/t5/Report-Server/Row-Level-Security-in-Power-BI-Report-Server/m-p/400525#M5227

mqassas · Answer

Thank you for your reply, I really appreciate this workaround!&nbsp;But unfortunately this is not working because I can't control the data source&nbsp;authentication type for Power BI report,&nbsp;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.&nbsp;Please, If you have any workaround for this please post it!

davidm5 · Answer

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.&nbsp;&nbsp;&nbsp;If you can't do that, the only other approach I know of is to build custom RLS in DAX.&nbsp; There's a brief bit of explanation on that approach in the thread I linked to.&nbsp; 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&nbsp;The big drawback I see is that your RLS is based on a visual level filter, which users can change.&nbsp; 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.

Forum Discussion

Pass measure to SQL Query in Power BI

5 Replies

Related content

Power Query dates

Data transform using power query

ayuda en power query

POWER BI DAX MEASURE

Power BI downloaded PBIX does not contain Power Query queries