Compare rows to measure to display records based on user

[JordanBieber]

My goal is to provide a function for users to instantly filter for rows related to them, based on the UserName()

First step was to make a Measure:

CurrentUser = UserName()

From here, I would like to do a comparison on another table to have a column that indicates whether or not the row is "related to the person" if a field contains their user id. Such as:

ThisIsMe = IF(ISERROR(SEARCH([CurrentUser], 'DATATABLE'[OwnerID])),"No","Yes")

I cannot create this as a column, I get:
<!> CUSTONDATA, USERNAME AND USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in Measures or in the AllowedRowsExpression.

I can create it as a Measure if I add MAX function around the table data...

ThisIsMe = IF(ISERROR(SEARCH([CurrentUser], MAX('DATATABLE'[OwnerID]))),"No","Yes")

but I cannot use this measure for much, not even in a slicer.

Any good ideas on how to compare the UserName() value to text in your data tables(and make that comparison usable)?

[v-shex-msft · Accepted solution]

Hi @JordanBieber,

You can try to use below formula to check username:

IsMe = IF(CONTAINS(VALUES(Sheet2[Login]),[Login],USERNAME()),"Yes","No")

In addition, if you want to use username function to dynamic filter records, I'd like to suggest you use RLS with usernam.

Reference:

Dynamic Row Level Security with Power BI Made Simple

Regards,

Xiaoxin Sheng

[Anonymous]

You won't be able to do this by creating a column, as those values are calculated when the data is refreshed.  Thus it was be person independant.  What you will need to do is make use of a calculate statement.  How about something like this?

My Measure = 
VAR MyName = 'Your Table'[CurrentUser]
RETURN
Calculate(
	<Thing you want the measure to show>,	
	'Your Table'[OwnerUserName] = MyName
)