Created a simple PBIX testfile with two measures:
- myUsername = USERNAME()
- CurrentDT = NOW()
This measures display fine in PBI Desktop and, after publishing, in PowerBI.COM
Unfortunately, when i use the feature "analyse" in Excel and put the measure into values area of pivot-table, the measure myUsername gets numeric scrambled and looks like this:
Can anyone confirm this as a bug?
or do i need a special formula to use username() to work/display correctly in excel or any other OLAP Client?
As by Information Design requirements we have to dump the "who" information in every reports header, so it would be really helpful ithis is possible.
>>Can anyone confirm this as a bug?
Based on test, I find the username function has the different result from desktop and service side, you can use USERPRINCIPALNAME function to work through this issue.
For detail infromation, you can refer to bleow article:
On the desktop, the USERNAME() function returns the user’s domain login (domain\login). However, when the model is deployed to powerbi.com, Username() returns the user principal name, which typically (but not always depending on how your AD is set up) is the user’s email address. To simplify dynamic security based on the user identity, DAX introduces a new USERPRINCIPALNAME() function that can help you secure on a column that has the user principal name. This avoids having to use an OR filter to support both deployment scenarios.
Notice that if your computer is not joined to a domain, both USERNAME() and USERPRINCIPALNAME() return the same thing (domain\login).
thanks for the hint with USERPRINCIPALNAME but this was not the questions!
The questions is: Why do DAX-Measure like USERNAME() not display correctly when you connect to the PowerBI Dataset with a MSOLAP.7 Connectionstring (aka. "Analyse in Excel"). Is this a bug in the MSOLAP.7 Provider oder in PowerBI.com ?
Below is my test result, the functions work well on my side.
I test on office 2016 and power bi desktop 2.41.4581.361 64-bit (November 2016).
Which version of excel you used?