UserName() DAX function returns a GUID on PowerBI

To piggyback on this issue, we could pass the username back via a StoredProcedure input parameter and pre-filter the data before it hits PowerBI. In the April Desktop Release we Define Query Parameters:

In Edit Query

Click Manage Parameters button

New Parameter

Save

Then right click parameter in left margin list of queries

Choose “Enable Load”  

Create another Query (SQL style) and copy and paste your Execute sp command with sql sp params hard coded to some sample values

After creating, previewing and saving (load)

Now go back to the formula bar of your query and interrupt the DAX with a concatenate phrase

Example my PowerBI parameter name is pOrgUnit   (btw PowerBI /DAX is case sensitive)

My original SQL Execute command

DECLARE       @return_value int

EXEC   @return_value = [dbo].[sp_AccountComparisons_InSummary]

              @CompanyDB = N'TWO',

              @userid = N'User1',

              @OrgUnit = N'100'

 SELECT 'Return Value' = @return_value

I replaced       @OrgUnit = N'100'

With          @OrgUnit = N'”&pOrgUnit&”'

Resulting DAX Formula

= Sql.Database("dynbuddev", "DynamicBudgets", [Query="DECLARE#(tab)@return_value int#(lf)#(lf)EXEC#(tab)@return_value = [dbo].[sp_AccountComparisons_InSummary]#(lf)#(tab)#(tab)@CompanyDB = N'TWO',#(lf)#(tab)#(tab)@userid = N'dynbudsm',#(lf)#(tab)#(tab)@OrgUnit = N'"&pOrgUnit&"'#(lf)#(lf)SELECT#(tab)'Return Value' = @return_value#(lf)"])

Now the clunky part is that you need to go to EditQueries, choose edit parameters, and then change from one param value to another

In my example changing from 500 to 100…

Changing the parameter can be invoked from either Edit Query button in either the Edit Query window or a Report

 I would expect a user to want to change focus by clicking a visual element, not a ribbon option, so I’m next going to try to just pass the username() as my @UserID sp  parameter.

If I can just pre-filter the list to what the user has security access rights to then I don’t care what they click on within the report because it has already been prefiltered to only their respective data they have security clearance to see.

We already have have complex financial account level security defined in our onpremise data source, I don't want to replicate that in Role Level Security in PowerBI...thus why the sp param approach for pre-filtering 

So to try to use this in the real world, I might still need to setup role level security to just be able to reference a Username()  instead of a GUID…

The key I suppose is that I Need the dataset  to load on-demand and not have any cached data (direct query or Odata)?

Does ODATA work with SPs?