UserName() DAX function returns a GUID on PowerBI

[DominiqueGire]

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 ?

[Shawn_Eary]

---

@DominiqueGire wrote:

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.

Actually for me, USERNAME() is returning a GUID when I invoke it from a measure on Power BI Desktop.  This seems like a bug. 

I have some data in a Power BI Push DataSet with Historical data analysis that is commited via REST from various users, I need to filter that Streaming DataSet to the current user to make the user experience more friendly [1].  Unfortunately, my understanding is that it's not possible to apply row level security to Push DataSets.

[1] - At this point, it's not a security risk if another user sees the data input by a different user, but it does look ugly.

[Brian_M]

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.

[zgidwani]

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?

[MarcNavarro]

@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.

---

[Jexah]

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]

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? 

[Johnny]

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)

[Anonymous]

Hi Johnny,

Could you please put some example how you did that?

Appreciate your help!

Regards,

Niket Talati

[Brian_M]

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

[PaulaS]

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]

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.