I have the following dax:
I want to subtract customer f2g2h2i2 store credit balance from customer a1b1c1e1
Measure = CALCULATE ( SELECTEDVALUE ( 'sales'[account_credit] ), 'sales'[customerId] = "a1b1c1e1", 'sales'[credit_type] = "store" ) - CALCULATE ( SELECTEDVALUE ( 'sales'[account_credit] ), 'sales'[customerId] = "f2g2h2i2", 'sales'[credit_type] = "store" ) + 0
When I try this I get the following error:
The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.
How can I optimize this so that it does not need to scan everything?
And is the 'sales' table in DirectQuery mode? For DirectQuery, there's a one-million-row limit for cloud sources, with on-premises sources limited to a defined payload of about 4 MB per row (depending on proprietary compression algorithm) or 16MB data size for the entire visual. When a generated query retrieves an excessively large number of rows from the back-end data source, the error "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows." occurs. This situation can occur with a simple chart that includes a very high cardinality column, with the aggregation option set to Don’t Summarize. The visual needs to only have columns with a cardinality below 1 million, or it must apply the appropriate filters.
Community Support Team _ Jing
1) Is it a calculated column or a measure?
2) How many rows and column has the table Sales?
3) Do you have an example of the visual you are trying to make?
You can try removing columns with High cardinality (Number of distinct values). This will reduce the size of the model, thus improving the performance of any scans on the table.
Learn how to create your own user groups today!
Click here to read more about the October 2021 Release!
Check out the new Power Platform Community Connections gallery!
Mark your calendars and join us for our next Power BI Dev Camp!