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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tucker
Helper I
Helper I

USERNAME() function DAX

Hey guys,

I'm trying to use either =username() or =principleusername() with RLS. My issue is that I have created a dataset as a SQL View and brought this into the desktop, I then created a tbl and imported from Excel with the users name and email address as this didn't exist in my original View. This is then joined using the users name.

I then apply the roles in the desktop and test and all works perfect, as soon as I publish up to the PBI service the RLS doesn't work anymore. I have a fairly good understanding of RLS and have it working for regions without an issue.

I guess this maybe something to do with the AD but unfortunately that's well past my understanding! Can anyone advise what I'm doing wrong pls?

Thanks in advance PBI peeps Toby
1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi @Tucker,

 

According to the official document, the format of user name in Service is UPN, which looks like an email address admin@contoso.com. So,

1. The list of user names should be UPN, which we can find out the exact format in the Service with function userprincipalname().

2. We should use userprincipalname() in the role we defined in Desktop.

 

>>Within the Power BI service, username() and userprincipalname() will both return the user's User Principal Name (UPN). This looks similar to an email address.

 

My test:

 

formula of a role:
IF (
    USERPRINCIPALNAME () = "test@contoso.com",
    [Column1] < 1000,
    [Column1] = 0
)

USERNAME() function DAX.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

Hi @Tucker,

 

According to the official document, the format of user name in Service is UPN, which looks like an email address admin@contoso.com. So,

1. The list of user names should be UPN, which we can find out the exact format in the Service with function userprincipalname().

2. We should use userprincipalname() in the role we defined in Desktop.

 

>>Within the Power BI service, username() and userprincipalname() will both return the user's User Principal Name (UPN). This looks similar to an email address.

 

My test:

 

formula of a role:
IF (
    USERPRINCIPALNAME () = "test@contoso.com",
    [Column1] < 1000,
    [Column1] = 0
)

USERNAME() function DAX.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale,

 

Thanks for you reply, I have got this far but after playing all day yesterday but still have an issue. 

 

So in my data I have the email address (UPN) of the user so have created a role and said [eMailAddress] = username() in the desktop. Published up to my Workspace and in Security selected "Test as Role" and I see exactly what I'd expect.

 

The issue then comes when I create a Content Pack and share with a O365 group (having added the user names to the RLS in the dataset), I then go into the group and "Get Data" when I then view the report in the group I can see everything.

 

I have read that the users need to be "Read Only" in the group which they are but I'm the Admin so don't know if that makes a difference. I got a collegue to test and he see nothing just blank reports.

 

Thanks again for your reply and if you know what I'm doing wrong please let me know cause I've been at this for weeks!!

 

Cheers Toby

@Tucker

 

Hi Toby,

 

I wonder if you have published an App. If so, everyone has to get a role even no filter applied to him/her. This is from my test. Have a try please.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft

 

Thanks, please forgive me as I'm very new to Apps and have always used Content Packs which it looks like they are going anyway.

 

So I created a new App Workspace and add my team members (this has created a new O365 Group as well which is frustrating as I already have my O365 groups with shared calendars, SharePoint sites etc!)

 

I then Published my PBIX file up to the new Workspace instead of via My Workspace and it appears, however this still doesn't seem to have solved my issue but I may be doing something wrong

 

Cheers TB

All,

 

Thanks for your help, all seems to be fixed and is now in UAT 

 

TB

Hi Toby,

I am not sure what was your resolution. Can you please share with us? Thanks.

Woody

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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