Problems with Max() and RLS/Using Max() with RLS to filter Summarized table

[Anonymous]

Hello experts,

I once more need your help.

Setting:

2 Tables (Companies, Transactions)

Using RLS to filter companies with an ID
Both connected with DirectQuery

Relationship 1:N

Transaction table has about 1000M rows, after SQL query about 50M.
Companies table has about 500 rows, after SQL query 500 rows.

Problem:

To simplify things, I'd like to summarize the Transactions-table. Doing so I get the error "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."
Therefore, I'd like to summarize the table, filtered by the CompanyID, which is active with the chosen role.
Filter looks like this: Filter(Transactions, Transactions[Company ID]=max(CompanyID)) -> Max(CompanyID) should be de ID from the active role. But here's the problem, the ID is the maximum ID of all companys. So RLS does not seem to work here?

A simplified File (but it'll do the trick): https://1drv.ms/u/s!AvY2chh-2ueehVY4drxTHmz9nUpy?e=wp8nqU
This is what it shoud look like (manually typed in the 1 for the company ID)

This is how it looks when using Max() for getting the Company ID.
As you can see, the ID should be 1, but it is 4.

Hopefully somebody can help me here.
Best regards

[Anonymous]

I see what you mean now. LOOKUPVALUE appears to have unfiltered access to your table, bypassing RLS. I'd suggest reporting this to Microsoft as a bug.

In your report, I'd suggest using measures of this form:

Measure =
VAR EmployeeLocation =
LOOKUPVALUE(Employee[LocationKey],
Employee[UserPrincipalName],
USERPRINCIPALNAME())
RETURN IF(ISFILTERED(Location[Location]),
<expression>,
CALCULATE(<expression>,
FILTER(Location, Location[LocationKey] = EmployeeLocation)))
That way it should default to calculating values for EmployeeLocation when Location is left unfiltered and will behave normally otherwise.

[parry2k]

@Anonymous I don;t know why you are using MAX etc, here is the expression:

Trans-summ-Issue = 

    SUMMARIZE(Transactions,Transactions[Company ID],
    "Rev4200",[TotalRev4200],
    "Rev4500",[TotalREV4500])

and here is the output

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Hello @parry2k,

thank you for your response.

I need the "max" part since I get the error which I stated in the question above.

The dataset I provided is just a simplified model with much less data.

Best regards

[parry2k]

@Anonymous did you saw the result I posted, is that what you are looking for? If not then let me know what the output suppose to me.

[Anonymous]

@parry2k yes, it works in the model which I provided. But it does not work in my real model because there are too much rows to

calculate.

This is a screenshot with the error from my real model.

This is why I need to limit the summarize function to this single company. If I don't limit the function, I get the error.

As far as I can see the problem is that the max() statement does not respect the RLS limitations. Is there any solution to this?

Thank you

[parry2k · Accepted solution]

@Anonymous this is totally different issue, you need to look at this aggregation approach instead of what you are doing. it is not a correct approach.