locked
What value does USERNAME() DAX function return in Power BI RRS feed

  • Question

  • I need to use the USERNAME() function in Power Pivot. I want to know what value would it return when I upload the excel file to sharepoint. Unfortunately there is no way to see this value.

    (I wonder why I cannot execute this function in Row Context. It would make all the sense to execute this query in row context and use it in some calculated column.)

    Thursday, December 18, 2014 1:18 AM

Answers

  • 1) There is no support for row-level security in the Power Pivot model. If this is a requirement, use Tabular. You cannot implement effective security at the Power Pivot layer, particularly on SharePoint, since the refresh action will always run under the authority of the service account.

    2) Measures can return any scalar value, not just numeric values.

    3) Your intention is not achievable with Power Pivot. Do not try to do security in the Power Pivot layer.

    Thursday, December 18, 2014 5:59 PM

All replies

  • If used in a calculated column, this function would only ever show the name of the user who refreshed the workbook model (Power Pivot) or SSAS cube (Tabular). This may be your intention, but it is not the use case that we've ever had (and I work in a consulting firm, so we've seen a good number of different use cases).

    Typically USERNAME() is only used in Tabular when setting up row-level security. It returns the username of the currently logged on user, which in Sharepoint may depend on your configuration, but if you have Kerberos set up and you're running all windows, it should be <domain>\userid.


    Thursday, December 18, 2014 3:20 PM
  • Yes, my intention is to implement a kind of row level security. However, I am not able to figure out the actual return value of this function for a logged in user when I upload the excel file with PowerPivot to office 365.

    There are a couple of challenges.

    a) UserName() function is not supported in row context. So, I cannot use this function in calculated columns. If I were able to do that, things were simple. I would have created a calculated column and seen the actual return value of the function easily.

    b) This function is only supported while calculating the measure. The output of a measure can only be numeric, so again I cannot see the return value of this function there. I tried something like this: IF(USERNAME()='domain\user',1,0) in order to guess the return value but I am not able to guess the actual return value.

    So, the question boils down to.. what is the return value to expect from this function at office 365.

    I hope that clarifies the question.

    Thursday, December 18, 2014 5:48 PM
  • 1) There is no support for row-level security in the Power Pivot model. If this is a requirement, use Tabular. You cannot implement effective security at the Power Pivot layer, particularly on SharePoint, since the refresh action will always run under the authority of the service account.

    2) Measures can return any scalar value, not just numeric values.

    3) Your intention is not achievable with Power Pivot. Do not try to do security in the Power Pivot layer.

    Thursday, December 18, 2014 5:59 PM
  • Thanks for your response.

    "Measures can return any scalar value, not just numeric values." helped me at some other place. Somehow, PowerView was not showing the measure that had textual output. But, pivot table did.

    Thursday, December 18, 2014 9:31 PM