Error while implementing dynamic row level security

[adharia]

Hi,

I am trying to implement dynamic row level security on my reports. I followed the entire instructions that has been provided in https://community.powerbi.com/t5/Community-Blog/Different-approach-to-Dynamic-Row-Level-Security/bc-... .

Also I tried performing following steps:

1. I created Manual table with 3 columns USER, EMAIL, Departments
2. I executed this Dax query  "=IF(CUSTOMDATA()="OK", "Correct Custom data in connection string", "No custom data in connection string property or unexpected value") "
3. After this I executed this query “Username = IF(CONTAINS(Roles,Roles[USER], USERNAME()), "Allowed", BLANK())” this as per my tables
4. Last I tried to create a custom column with this query Security_Department = NOT(ISBLANK(LOOKUPVALUE(Roles[Departments],Roles[Departments],[Department],Roles[EMAIL],USERNAME())))

It still throws me error “ CUSTOMDATA, USERNAME and USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in Measures or in the AllowedRowsExpression.”

Could anyone please provide me with a possible workaround for the error?

Thankyou In Advance

[v-huizhn-msft]

Hi @adharia,

Please notice the issue have been asked on the comments of the article you posted. The author said:

Username() function can't be used in calculated column. So you should rather put the final formula into role row level filter directly.

Paste this expression into role definition and it will work

NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],USERNAME())))

When publishing to PowerBI.com

 NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mappi

---

The solution is not effective to you? Please review the first and second comments, please post feedback when you have still other problem.

Best Regards,
Angelia