Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?

 

 

 

Need Help

6 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey, can you give an example? Thanks

  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        m3tr01d
        Icon for Continued Contributor rankContinued Contributor
        5 years ago

        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.

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    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