Skip to main content

Power BI

Needs Votes

Provide username function in Power Query - M or DAX calculated columns

Vote (430) Share
's profile image

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)
's profile image Profile Picture

Power BI User on 05 Jul 2020 23:17:26

RE: Provide username function in Power Query - M or DAX calculated columns

Team, any updates for using current username in calculated column. This is a critical functionality in implementing Row level security dynamically.

's profile image Profile Picture

Arpit on 05 Jul 2020 23:15:58

RE: Provide username function in Power Query - M or DAX calculated columns

Any updates for using the current Username () in calculated column it is very important functionality for using Row Level Security dynamically.

's profile image Profile Picture

Дмитрий Козлов on 05 Jul 2020 23:07:22

RE: Provide username function in Power Query - M or DAX calculated columns

It is not good to require a Pro License for enable that function (RLS with Pro License)

's profile image Profile Picture

ksabi on 05 Jul 2020 22:55:39

RE: Provide username function in Power Query - M or DAX calculated columns

I created this post concerning the case where this function is needed:

http://community.powerbi.com/t5/Desktop/How-to-get-filtered-measure-in-another-table-column/m-p/220176#M97729

's profile image Profile Picture

Sebastian on 05 Jul 2020 22:55:37

RE: Provide username function in Power Query - M or DAX calculated columns

I need this too!!! What is the status on this feature!

's profile image Profile Picture

ksabi on 05 Jul 2020 22:55:15

RE: Provide username function in Power Query - M or DAX calculated columns

This function would be a great improuvement of dax. any news for this idea?

's profile image Profile Picture

Lyle Spencer on 05 Jul 2020 22:51:28

RE: Provide username function in Power Query - M or DAX calculated columns

We very much need this. This would allow us to provide security and context for many of our visuals. The role based RLS is not scalable when you have thousands of employees also the RLS data already lives in our database we just need to pass it the current user to retrieve data they are authorized to see. Another issue is often this data is sources from transactional finance tables with millions of rows. Pulling down the whole table then applying Power BI RLS after the fact is often again not practical.

's profile image Profile Picture

Lyle Spencer on 05 Jul 2020 22:51:28

RE: Provide username function in Power Query - M or DAX calculated columns

Also, the comments below do not address what Brian is trying to do. Power BI RLS is post query, not at query time. Also the solution offered by anonymous to "=Sql.Database("(localdb)\v11.0","",[Query="SELECT SYSTEM_USER"])
should do it" will not work as most queries, particularly if going through a gateway will have the service account context not the user running the report.

's profile image Profile Picture

Imke Feldmann on 05 Jul 2020 22:26:18

RE: Provide username function in Power Query - M or DAX calculated columns

Agree and would like to take this one step further and specificly request for this feature for M: In order to filter the import into the data model already. This would speed up load time, simplify user experience, make the models faster and files slimmer...

's profile image Profile Picture

Imke Feldmann on 05 Jul 2020 22:26:18

RE: Provide username function in Power Query - M or DAX calculated columns

Hi Jon, your requirement seems to be covered here: https://insightsquest.com/2016/07/20/role-based-default-filter-context-for-power-bi/comment-page-1/#comment-84