USERNAME() and USERNAMEPRINCIPAL() not working on published report.

[Lightbot]

Hi,

I'm trying to implement user based filtering using RLS as described here:

https://community.powerbi.com/t5/Community-Blog/Different-approach-to-Dynamic-Row-Level-Security/ba-...

But as a test I have 2 measures and 2 custom columns, with one each of USERNAME() and USERPRINCIPALNAME(), the measures work in the desktop, but all 4 are showing errors on the published report.

Am I using these wrong or are they broken?

[Seth_C_Bauer]

@Lightbot Have you applied users to the roles in the Service? Go to the dataset -> Security and you need to apply different users or groups to the roles you've created.

[Anonymous]

So...  are you saying for the filter on User() I have to add ALL users to the Row-Level Security?  Wow!  that is over 250 people!   Or, just those with advanced rights in the workspace?

[Lightbot]

@Seth_C_BauerI've only applied myself at the moment, but I shouldn't need that for a single field table that just shows USERNAME() from a calculated column or measure should I?

Thanks

[Anonymous]

Hi @Lightbot,

What error message do you get in Power BI Service? Based on my test, when I create column using the formula in the blog, I get error message” CUSTOMDATA, USERNAME and USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in Measures or in the AllowedRowsExpression”.

Security_Category = NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[EMAIL],USERNAME())))

In addition, you apply yourself as RLS role member in Power BI Service, right? Please note that by default you have Admin rights to the model when you create the Power BI Desktop file and upload it, you will be able to see everything in the reports, thus to check if the RLS works as expected, please share your dashboard with other user who has limited access to data in reports, for more details, please review this similar blog.

Thanks,
Lydia Zhang

[Lightbot · Accepted solution]

Hi @Anonymous,

When I signed into the report yesterday the measures were working fine, so my initial problem is solved.

Thanks for the info about the admin rights, I had wondered that but the confirmation has helped testing.

I've managed to solve my security problem using a simpler RLS:

[Category] = LOOKUPVALUE(security_mapping[Category], security_mapping[Email], USERNAME())

Thanks for the help.