Forum Discussion

pratk30's avatar
pratk30
Advocate I
8 years ago
Solved

Error on using UserName() function in custom table

Hi Folks,

 

I am getting below error after using measure which stored USERNAME().

CUSTOMDATA, USERNAME and USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in Measures or in the AllowedRowsExpression.

 

Measure

CurrentUser = USERNAME()

 

Custom table

LingeringDS = FILTER(AssignmentTimephasedDataSet, AssignmentTimephasedDataSet[ResourcesOwner.ResourceEmailAddress] = [CurrentUser])

 

I need to filter one table data with current logged in user's email id  in both Power BI desktop and Service.

RLS is one the option but not suited to my requirement.

 

Can anyone plase help me on this.

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi pratk30,

     

    As we can see from the error message, it isn't supported. What's your purpose? Maybe there are other approaches. For example, we can use a measure like this, then we can filter out these 0s in the Filters.

    Measure =
CALCULATE (
    IF (
        MIN ( 'AssignmentTimephasedDataSet'[ResourcesOwner.ResourceEmailAddress] )
            = USERNAME (),
        1,
        0
    )
)

    Best Regards,

    Dale

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi pratk30,

     

    As we can see from the error message, it isn't supported. What's your purpose? Maybe there are other approaches. For example, we can use a measure like this, then we can filter out these 0s in the Filters.

    Measure =
CALCULATE (
    IF (
        MIN ( 'AssignmentTimephasedDataSet'[ResourcesOwner.ResourceEmailAddress] )
            = USERNAME (),
        1,
        0
    )
)

    Best Regards,

    Dale

    • Shawn_Eary's avatar
      Shawn_Eary
      Advocate V
      6 years ago
      v-jiascu-msft wrote:

      Hi pratk30,

       

      For example, we can use a measure like this, then we can filter out these 0s in the Filters.

      Measure =
CALCULATE (
    IF (
        MIN ( 'AssignmentTimephasedDataSet'[ResourcesOwner.ResourceEmailAddress] )
            = USERNAME (),
        1,
        0
    )
)

      Best Regards,

      Dale

      Fortunately, I got this to work for me, but I find it rather unintiutive...  My beginner DAX mind says the following should have been sufficient:

      IsCurrentUser = 'AssignmentTimephasedDataSet'[ResourcesOwner.ResourceEmailAddress] = USERNAME()

      Unfortunately, DAX does not seem very intuitive as far as I can tell.







    • pratk30's avatar
      pratk30
      Advocate I
      8 years ago

      Hi v-jiascu-msft,

       

      It worked for me, but as per this solution i need to create this measure for every table where email id is and need current user. Right?

       

       

      Regards,

      Prateek Mangal

    • ToddChitt's avatar
      ToddChitt
      Super User
      6 years ago

      v-jiascu-msft 

      I have a very similar situation and create the same measure. But when browsing the cube via SSMS, I get the error: 

      The function MIN takes an argument that evaluates to numbers or dates and cannot work with values of type String.

      So I'm switching to FIRSTNONBLANK.

       

      But this creates a MEASURE, and I can't use that in a FILTER on a page.

      Any advice is appreciated.