cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Forecaster
Helper I
Helper I

How to optimize my dax query?

Hi everyone 

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?

 

 

 

6 REPLIES 6
v-jingzhang
Community Support
Community Support

Hi @Forecaster 

 

See if this link helps: The resultset of a query to external data source has exceeded the maximum allowed size of ‘1000000’

 

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.

 

This article may help: Power BI — How to fit 200 million rows in less than 1GB! | by Nikola Ilic | Apr, 2021 | Towards Data...

 

Regards,
Community Support Team _ Jing

m3tr01d
Resolver III
Resolver III

Hi @Forecaster 

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?

1.) It is a measure

2.) >50,000,000 rows, 25 colums

3.) the visual is just a card that shows the value returned

Hi,

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.

aj1973
Community Champion
Community Champion

Hi @Forecaster 

You can use filters in your calculations, like filter dates.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hey, can you give an example? Thanks

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!