Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
Solved! Go to 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
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
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.