1000000 row limit error when viewing report using RLS (Row Level Security)

[mcalasanz]

Hello,
I'm experiencing an issue with the stacked area chart in which I get the error message 'The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.' within PowerBI Desktop.   The error is happening as I was testing RLS (Row Level Security) within PowerBI Desktop. That is, I click on 'View as Roles' and enter the 'Other user' called test@test.com (as an example) and select a role I created called 'Tenant'.  The 'Tenant' role that was created is using the DAX expression [UserName] = Username(). 

Now when I 'Stop Viewing' as that user the stacked area chart renders correctly.  I'm not too sure what is causing this error and I can only attribute it to RLS.  Also, other visualizations are affected by this. 
Here is a little more detail about this report:

1. It is using Direct Query.

2. The axis in the stacked area chart is using a date hierarchy that I had to create because it is my understanding that it is not supported by default if a report is using Direct Query.

3. I've submitted a frown on this from my report.

Please let me know if you need anything else from me.  Your help is much appreciated
Thank you

[v-qiuyu-msft]

Hi @mcalasanz,

For DirectQuery, there is a fixed limit of one million rows placed on the number of rows that can be returned in any single query to the underlying source. 

In your scenario, please check if there are more than one million rows for the user called test@test.com. If it is, please add some filters for the visual, see: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#important-considerations-when-usin...

Best Regards,
Qiuyun Yu 

[mcalasanz]

Qiuyun,

I do have a slicer on the report and filtering the data by a date dimension that I know is less than one million rows (I verified it by querying the data in SSMS using a date range). Is that what you were referring to when you said that I should "add some filters for the visual?".  

[v-qiuyu-msft]

Hi @mcalasanz,

Yes, either using slicer visual or use visual level filters to filter data. 

Have you verified corresponding records for test@test.com less than 1 million rows in SSMS as well? 

Best Regards,
Qiuyun Yu 

[mcalasanz]

@v-qiuyu-msft

Yes, I've applied a date slicer visual filter on the report.  I have also verified in SSMS that the number of rows that should be returned is less than one million. So would applying the visual filter query the database again or does it work off the dataset in memory and the filter just applies to that particular subset of data and therefore that is why I'm getting the 1 million row limit error?

The one thing that doesn't make sense to me is that I seem to get this error when I view as a role in PowerBI Desktop.  When I stop viewing as that role then the visual renders correctly.

Any insight would be much appreciated.

Thank you

[SivaMani]

I'm also experiencing the same issue. The measure is working fine when I use this directly. But it returns an error when I use in an IF condition.