CALCULATE() function oversteps Row-Level Security rules applied to fact table

[Anonymous]

I am getting incorrect totals for a calculated column because row-level security filtering is happening after a CALCULATE() expression. 

I have read it's not ideal design to have RLS applied to the fact table, but I have an OR condition from two different dimensions that need to be applied. 

From my fact table of transactions, I have foreign keys to both a manager and partner dimension. My RLS rule is roughly:
RELATED(MANAGER[Email]) = USERPRINCIPALNAME() || RELATED(PARTNER[Email]) = USERPRINCIPALNAME()

I have a third dimension table, Client. I want to aggregate a total realization % for the Current Year only in the client table. I then want to bring this summary calculation into a column in the Client table so that I can create a dimension that can be used in a visual to segment overperforming/underperforming clients. 

To start, I built a calculation to return the appropriate sums only for the current year, of the form: CALCULATE(SUM(FACT_TABLE[Amount]), DATE_TABLE[YEAR] = DATE_TABLE[CURRENT_YEAR]).

But when I bring this calculation into a calculated column in the Client table, it ignores the Row-Level security filters, showing me the total for that client across all Managers and Partners. 

This is not the case when I create a new calculated column in the Fact table that only includes current year values. If I create this column, the RLS rules are properly applied. 

This column is of the form: IF(RELATED(DATE_TABLE[YEAR]) = DATE_TABLE[CURRENT_YEAR], AMOUNT, 0). 

Perhaps it's a Tableau background, but this order of operations is a little confusing to me, that RLS rules are applied after a calculated column is computed. I would expect that RLS rules are applied to all tables first. 

I would also expect that my filtering within CALCULATE() would essentially operate over a column of values equivalent to IF(RELATED(DATE_TABLE[YEAR]) = DATE_TABLE[CURRENT_YEAR], AMOUNT, 0). In either case, I think the expectation is that Row-Level Security truly means that any rows not meeting the RLS criteria are not available in any way to calculations, whether columns or measures.

Perhaps this isn't a bug, but I think the community would really benefit from a clear description of this filtering "order of operations" as Tableau has available. This behavior was entirely not clear to me until I did more extensive testing and it seems quite counter-intuitive to me. 

[v-cazheng-msft]

Hi @Anonymous 

Thanks for your description in details. And your problem is that the total value of a Calculated column isn’t changed according to the RLS. May I know whether you could provide a link of a sample pbix file containing your issue? Thanks in advance!

Best Regards,

Community Support Team _ Caiyun

[Anonymous]

Thank you, @v-cazheng-msft! 

I can't provide a sample PBIX, as it is internal data, but I will work up dummy data, if need be. 

My use case is that I want to cluster clients based on aggregations from a central fact table that is affected by RLS. The issue is that the client may fall into a different bucket, based on who is viewing the report, as clients have a one-to-many relationship to jobs, which have a 1:1 with a report viewer (either a manager or partner).

In pseudo-SQL, this would be the ability to calculate a table like:
SUM()
FROM TRANSACTIONS T
INNER JOIN JOBS J
ON T.JOB_ID = J.JOB_ID
INNER JOIN PARTNER P
ON P.PARTNER_ID = J.PARTNER_ID
INNER JOIN MANAGER M
ON M.PARTNER_ID = T.PARTNER_ID
WHERE USERPRINCIPALNAME() = P.PARTNER_EMAIL  OR USERPRINCIPALNAME() = M.MANAGER_EMAIL
GROUP BY CLIENT
As you can see, my problem would also be solved by being able to use USERPRINCIPALNAME() within a Table Calculation as well. 

Is there any way that I can have the RLS rules apply to the fact table before I compute these totals at the client level? 
My end result: I'd like to create a summary chart bar chart where I am able to group clients by "Underperforming", "Meeting expectations", etc., based on the aggregations from the transactions table. 

Importantly, I'd like this to be integrated with the data model. So, when a user clicks on a particular cluster, they are able to see a full list of the clients that fall within that grouping. 

This is something that I know could be accomplished with the same RLS rules and a single calculation within Tableau, so I'm stumped to not find a decent equivalent or approximation for this. The key feature here is that the output of calculations can be used for row-level filtering in Tableau, with level-of-detail expressions. 
I can think of ways that I could produce an accurate bar chart showing how many clients fall within each grouping using measures, but I can't think of a way to enable these groupings to act as a filter for charts broken out by client.

I have also looked at built-in clustering, but it appears this has the same pitfall of not adjusting for changes in RLS visibility.