cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DominiqueGire Frequent Visitor
Frequent Visitor

UserName() DAX function returns a GUID on PowerBI

When you use UserName() in a DAX function in PowerBI Desktop that works fine and shows something like "AzureAD\FirstnameLastname" if I use that function in a calculated measure to be displayed.


When you publish the PIBX to PowerBI, the UserName() function no longer shows the same thing and is replaced by a GUID or something that looks like this. I use PowerBI Profesionnal within Office365.

 

Any idea ?

10 REPLIES 10
PaulaS Frequent Visitor
Frequent Visitor

Re: UserName() DAX function returns a GUID on PowerBI

Yes, I have the same problem! 

UserName() works fine in PBI Desktop but shows something like "10037FFEBDC1B 3fe697b9-2e85-33…. " in PBI online.

I'm trying to filter rows using the actual user.  Is it possible using Power BI??

Thanks!

greggyb New Contributor
New Contributor

Re: UserName() DAX function returns a GUID on PowerBI

USERNAME() in the PBI Service returns the user name of the Microsoft service account for that instance of the server. (This is an educated guess, haven't had a concern to actually verify).

 

If the goal is to apply row-level security in Power BI, the only actually secure method is to use DirectQuery against a SSAS cube or a SQL data source that has row-level security. The Power BI Power Pivot model **CANNOT** support row-level security.

When performing a direct query, the Power BI Service will pass along the currently logged in user's credentials for authentication against the data source, returning only data that that user's role is allowed to see based on the security criteria.

Highlighted
Johnny Frequent Visitor
Frequent Visitor

Re: UserName() DAX function returns a GUID on PowerBI

The problem is "solved" after RLS has been implemented.

Once you "turn on" RLS the username function now returns your email-address (sign in address)

Brian_M
Advisor

Re: UserName() DAX function returns a GUID on PowerBI

Johnny, could you post an example of how to use the username() in the RLS?

talatiniket Member
Member

Re: UserName() DAX function returns a GUID on PowerBI

Hi Johnny,

 

Could you please put some example how you did that?

 

Appreciate your help!

 

Regards,

Niket Talati

Brian_M
Advisor

Re: 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.

 

 

Jexah Occasional Visitor
Occasional Visitor

Re: UserName() DAX function returns a GUID on PowerBI

Hi Brian,

 

Thanks, this was really helpful! Unfortunately, the whole reason I am using the USERNAME() function to implement RLS, is because I would like to be able to apply RLS on accounts outside of my organisation, and using a calculated table I can do this.

 

The issue comes in when you create a role, all extra-organisational email addresses cannot access the data, see: https://dl.dropboxusercontent.com/u/25095474/ShareX/2016/05/chrome_2016-05-26_09-57-13.png

 

Any ideas as to how to prevent the restricted data access when a role is enabled? Or perhaps a method of generating the hex string of Xs to compare to the USERNAME() function? (`XXXXXXXXXXXXXXXX yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy`)

Brian_M
Advisor

Re: UserName() DAX function returns a GUID on PowerBI

Hi Jexah,

 

The link to the image in your dropbox account doesn't seem to work.

 

I think I understand what you are saying, I'm trying to test that restriction this end. Are you saying that the external user's view of the USERNAME() function reverts back to a GUID if they try to access the report, or are they blocked from seeing the report entirely? 

 

 

zgidwani Visitor
Visitor

Re: 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

 

ManageParmeters_NewParam.png

 

Then right click parameter in left margin list of queries

Choose “Enable Load”  

 

EnableLoad.png

 

 

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…

 

EditParam1.png 

 

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

 

 EditParam2.png

 

 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?