Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

How to make dynamic table/column, based on Username

Hi,

 

I'm trying to create a calculated column to my table, which contains the value of another existing column, if the current user's username is contained in a third column containing the allowed users' usernames, and if it doesn't, it shows a given text.

 

So my table should look like this, if user1@email.com views it:

table1

column1allowed userscalculated
xyzuser1@email.comxyz
yyyuser2@email.comaccess denied

 

The point is, I want to show the value of a column based on the allowed users column and the logged in user.

 

My current DAX code looks like this:

Calculated =
IF(
CONTAINSSTRING(SELECTCOLUMNS( table1,"Allowed",[Allowed users]),USERPRINCIPALNAME())
, SELECTCOLUMNS( table1,"Szöveg",[Column1]),"access denied"
)
 
This is not working because USERPRINCIPALNAME() can't be used in calculated columns.
 
Are there any workarounds to this problem?
 
Many thanks!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , USERPRINCIPALNAME() can be used in measure not column

 

So you can have like

 

maxx(table1 , IF(
CONTAINSSTRING(SELECTCOLUMNS( table1,"Allowed",[Allowed users]),USERPRINCIPALNAME())
, SELECTCOLUMNS( table1,"Szöveg",[Column1]),"access denied"
)

 

and use in aline level visual

 

 

also, explore RLS

 

How to use Row Level Security (RLS): https://youtu.be/NfdIA0uS6Nk

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Does the above reply solve the problem?

If it works, please consider to mark it as a solution.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@Anonymous , USERPRINCIPALNAME() can be used in measure not column

 

So you can have like

 

maxx(table1 , IF(
CONTAINSSTRING(SELECTCOLUMNS( table1,"Allowed",[Allowed users]),USERPRINCIPALNAME())
, SELECTCOLUMNS( table1,"Szöveg",[Column1]),"access denied"
)

 

and use in aline level visual

 

 

also, explore RLS

 

How to use Row Level Security (RLS): https://youtu.be/NfdIA0uS6Nk

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors