Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous 

 

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 Science

 

Regards,
Community Support Team _ Jing

m3tr01d
Continued Contributor
Continued Contributor

Hi @Anonymous 

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?

Anonymous
Not applicable

1.) It is a measure

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

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

m3tr01d
Continued Contributor
Continued Contributor

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 @Anonymous 

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

Anonymous
Not applicable

Hey, can you give an example? Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.