Power BI
Needs VotesProvide username function in Power Query - M or DAX calculated columns
on 20 May 2016 01:15:53
Being restricted to just using username () in measures (or AllowedRowsExpression) means that you cannot use the context of the current user in slicers or page level or report level filters.
Just to be clear, the desired result is not data security (that can be done through roles), it is to provide relevance. We want to be able to show a page that shows (for example) My Teams timesheet hours, perhaps next to a report page that shows an overview of the entire company.
You can provide some neat filtering using measures containing username () , but in particular, you can’t provide slicers (or page filters) that are cut down to just the list of relevant items based on the user logged in.
It would be extremely helpful if you could access the username value either via a function in PowerQuery M, or to allow username () to be referenced in a calculated column using DAX.
Trying to use username () in a calculated column currently gives the error:
"CUSTOMDATA and USERNAME functions are not supported in calculated columns. These functions may only be used in Measures or in the AllowedRowsExpression."
Perhaps there are security, functional or performance reasons why this isn't possible, but thought it worth putting forward.
- Comments (41)
RE: Provide username function in Power Query - M or DAX calculated columns
Given that PowerBI is based on Analysis Services, could we ask for an implicit "Current User" dimension, perhaps with a single hierarchy "User Details", with two levels :
1: User Details (with a single member = the current user) and Attributes: User Name, User Principal Name, User Distinguished Name
2) Group Membership: (Multiple members, one per group the current user is a member of) and Attributes: Group Display Name, Group Distinguished Name
PowerBI Desktop must be able to "inject" this dimension into every workbook, as it has knowledge of the desktop user, and if the workstation is part of a domain , it can query and populate the Group memberships
PowerBI Services should also be able to create the equivalent, as it is AzureAD aware by definition.
So "package" this concept into PowerBI, and make it available to users.
RE: Provide username function in Power Query - M or DAX calculated columns
To avoid having too much and also unnecessary data sent to clients
RE: Provide username function in Power Query - M or DAX calculated columns
To avoid having too much and also unnecessary data sent to clients who own no privilege of having it and filtered with DAX RLS, or setting up thousands of login accounts in database engine to implement DATABASE RLS or using dmv to check login_name to filter rows, the only parameter-free way now is constructing SSAS solution which have built-in Role based row access control by setting up roles and adding domain groups/users in them. One core reason of the existence of this ability is the support of VBS in MDX, from which domain information can be accessed.
Yes for security reasons, the solutions for large user group reporting are limit.
So for database data source, either the DB team change the way Data Engine work to be like SSAS, or you power bi team adopt to the way Dataset in SSRS pagination report works.
I wonder how long you two are going to prolong with each other, it has been years so long.
RE: Provide username function in Power Query - M or DAX calculated columns
Does anyone know if this is even being considered?
Nice to get some acknowledgement of an idea with 340 votes...
RE: Provide username function in Power Query - M or DAX calculated columns
I found a way to implement this using a Bridge table. Here is the article on how to do it.
https://medium.com/@barrasa8/dynamic-data-masking-in-powerbi-based-on-rls-927eb6a34e5d
RE: Provide username function in Power Query - M or DAX calculated columns
I have been trying to do this in powerbi, and I tried all sort of tricks , without success, and it seems like powerbi does not allow to mask certain information depending on the user that is connecting to the report.
RE: Provide username function in Power Query - M or DAX calculated columns
Customers don't understand why this is not possible as it is a very common desired use case.
RE: Provide username function in Power Query - M or DAX calculated columns
It's insane that this feature isn't available... We've been trying to migrate from tableau to Powerbi, but without the ability to mask (NOT HIDE) data for GDPR compliance, we may need to go back
RE: Provide username function in Power Query - M or DAX calculated columns
Hi,
I too would need this feature, please.
With kind regards,
Bogdan