How to Use SQL Server Stored Procedures in Microsoft Powerbi

Power BI. Execute SQL Server stored procedure with a user parameter

Ok, I have found the solution.

1. Add a parameter and allow load for it. (for example its name is Query_param_1)
2. Create query in Import mode (only this mode allows it)
3. Then open extendet editor for created query
4. Edit "query part:
5. [Query="EXEC [rpt].[sp_rpt_bids_statictics_2] @DateFrom = '"Query_param_1"'"]
6. Now it works with the parameter, chosen by user.

Call a stored procedure from Power BI with a DAX parameter

I don't have a way to do what you're asking to do (grab the result of USERNAME() in DAX, then pass it as a parameter to a stored procedure in the Query Editor).

I also don't know of a way to grab current user information from the Query Editor directly, though this article has some recommendations that may work: https://querypower.com/2017/04/03/4-ways-to-get-username-in-power-query/

What I will say is that Power BI does include Row-Level Security features that I'd strongly recommend considering: https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

Row-level security can also be implemented in SSAS: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-row-level-security-onprem-ssas-tabular/

It would require changing your design so that instead of having a stored procedure that accepts a text parameter for the user and then returning data only for that user at the time that the user runs the report, your model would instead pull data for all users, and Power BI would then use RLS to filter the results for the person viewing your report.

An advantage of doing this is that everyone would be accessing a filtered version of the same underlying data model, so the data model itself does not have to fully refresh for each user that comes along (meaning less wait-time for end-users).

Another advantage is that you'd be using the built-in security tools that Power BI offers rather than rolling your own solution with your own mapping tables. (Not that your solution is bad but there's more risk and less support available if you roll your own solution based on your own mapping table.)

Since you're already aware of the USERNAME() parameter in DAX, I imagine you are/were already aware of RLS and had ruled it out (although it was fairly new at the time you asked your question). But since it wasn't mentioned already, I thought I should at least mention it for others who might be trying to do the same thing.

Related Topics