Error on using UserName() function in custom table

[pratk30]

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.

[v-jiascu-msft · Accepted solution]

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]

---

@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.

[ToddChitt]

@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.

[pratk30]

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

[v-jiascu-msft]

Hi Prateek,

I'm afraid so. 

Best Regards,

Dale