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 ?
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??
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.
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.
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`)
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?
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
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?
Find out who's part of the program this season, and welcome the new Super Users.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Learn about the exciting things that happened in July.
All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.
Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.