Forum Discussion
UserName() DAX function returns a GUID on PowerBI
It works as soon as you do the following:
1) Publish the report and dataset to the Power Bi service
2) Go to the dataset and click the ellipsis to the right and choose - SECURITY
3) Click Create New Role and press enter (do not bother renaming it, adding users, or defining the DAX expression)
4) Press Shift-F5 or click in the address bar along the top and press Shift-Return to refresh the page.
You should then find that the username shown in your measure is the login name for the cloud portal which in my case is also my Office 365 work account email address.
As you probably know, what you achieve isn't any sort of security lock down on the dataset based on the logged in user. For that you would have to define the Role and add users and a DAX express accordlingly.
Instead, what you have is the ability to deliver reports that show "My Team Sales", "My Timesheets", etc, based on the logged in user, which for me is the holy grail - being able to help the user hone straight in to the content that matters to them without having to find themselves in a long slicer full of names. Nothing worse than busy managers who are allowed to see a wide range of regions or business areas, getting lost when they try to see their own data.
It's seems a bit strange - enabling Row Level Security but not defining any proper roles or DAX expression, but I'm not complaining now that it works!!
Hope that helps.
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?
zgidwani finally do you found the way to pass username() by parameter?
zgidwani wrote:@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.
