Post PatronI'm currently preparing a slide deck on Dynamic RLS and came across your blog post.
I tested your solution. Whenever I want to replace the string value "domain\user" with the USERNAME() or USERPRINCIPALNAME() function, I get the following error:
"The CUSTOMDATA function and the USERNAME function are not supported in calculated columns. These functions must only be used in measures or in 'AllowedRowsExpression'."
If I use the string it works perfectly fine. Can you double-check this behavior in your model?
Data model with relationships and "security_mapping" table and calculated column "Security"
jirineoral wrote:To make it really dynamic, I will replace static value “domain\username” with function USERNAME() in formula.
DAX formula:
Security_Category = NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],USERNAME())))Function USERNAME behaves differently in desktop where it returns username in format “domain\username” and when published to powerbi.com where it returns email address [email protected]
Therefore before publihing replace column USER with column EMAIL in DAX formula
Security_Category = NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[EMAIL],USERNAME())))Conclusion
In this blog post I covered how to implement dynamic security in Power BI without relationships in data model using DAX formulas. Let me know if you have any questions or comments
Jiri
