topic Re: Calculated column checking the current RLS Role in DAX Commands and Tips

Calculated column checking the current RLS Role

Anonymous — Fri, 02 Apr 2021 10:34:12 GMT

Hello everyone, I'd like to add a calculated column to a table, setting TRUE or FALSE depending on the value of current role in use.
This table is not RLS filtered itself, but it must get some information from an other table contening a list of roles and which is filtered.

That seems simple but the problem is :

1) the DAX function ISFILTERED() doesn't detect that a table is RLS filtered;

2) PowerBi forbids you to use the USERNAME() function in a computed column (only in a measure - that is absurd), and it seems there is no function giving you the current role in use

3) apparently you cannot use a computed column (from an other table) to test the contents of RLS Filtered table (for example with IF() and CONTAINS() DAX functions).
It seems that during table load phase, the tables are first fully charged, and RLS filtering applies only after.
The result is that the computed column (checking for a certain role) allways returns TRUE, as during evaluation the RLS filtered table is found complete and not yet filtered.

This is not a simple issue. PowerBi Documentation is very poor about this subject.

Thank you in advance if someone has an idea.

Re: Calculated column checking the current RLS Role

amitchandak — Fri, 02 Apr 2021 11:32:45 GMT

Anonymous , Not very clear to me.

ISFILTERED() is to check if a column filtered by slicer or filters

Yes, Username and userprincipalname work only with measure

I am not sure I got your issue completely. But these are good RLS articles

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns-in-Power/td-p/712613
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/
https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi

Re: Calculated column checking the current RLS Role

Anonymous — Fri, 19 Nov 2021 18:38:53 GMT

Fully agree, Anonymous.

I am generally at a loss for something equivalent to Tableau's very clear description of its order of operations / query pipeline. I think this documentation will help clarify the nature of the question, amitchandak: https://help.tableau.com/current/pro/desktop/en-us/order_of_operations.htm

I did some similar testing to try and discover whether RLS filtering would be applied before or after evaluation of a computed column, finding that the column evaluation happens first, then RLS filtering is applied.

It would be great if this order were the other way around, which I think is also required to be able to use USERPRINCIPALNAME(), etc., within a computed column.

This would be great to solve for use cases where you want to use a centralized data model but be able to customize reports to a degree for different users. This could reduce a lot of duplicate work and development, as one single data source and report could cater to a greater variety of audiences.

In my particular use case, I want to populate a column DYNAMIC_DIMENSION with different dimensions based on who is logged in. Let's say if a top-level director is logged in, we want to show a bar chart broken out by Region and then a Regional Manager could be shown the breakout by offices.

This could be achieved in a computed column, alongside RLS rules, to populate that DYNAMIC_DIMENSION column accordingly. That column would, of course, be used as the axis for the bar chart.