The resultset of a query to external data source has exceeded the maximum allowed size of ‘1000000’

[NBOnecall]

Hi,

I am running visuliaztion that queries off of a DirectQuery and some imported tables in my PowerBi file. I can break down the parameters and run them fine by doing it based off of a category instead of all together. The total results of 3 lines items coming back cumulatively. I am guessing when I run all the categories together I exceed the 1,000,000 rows, but doing the categories serpate it hits less than that. I am just hoping to understand this better to try and figure out a way of troubleshooting the issue. .

Below is my visual and with my filters selected. My end result is to show me what item are out of stock and for how long during a date range for 4 locations. Right now I have just 1 day selected and the 4 locations, along with just one buyer's items. This total comes to 958 internalIDs, or count of unquie products and locations. I am having a hard time trying to understand why this would have a error of the 1,000,000 rows. Any idea? Am I missing something big, that is obvious?

Thank you, 

Noel

[v-alq-msft]

Hi, @NBOnecall 

Based on your description, the limit can occur in cases where Power BI is not fully optimizing the queries sent, and there is some intermediate result being requested that exceeds the limit. It can also occur whilst building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity would hit this limit if there were more than 1 million customers, until some filter were applied.

You may refer to the following links.

https://community.powerbi.com/t5/Desktop/DirectQuery-Data-source-has-exceeded-the-maximum-allowed-si...

https://community.powerbi.com/t5/Desktop/Error-Resultset-of-a-query-to-external-data-source-exceeded...

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener]

Hi @NBOnecall ,

maybe your measure creates a value for every possible combination.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[NBOnecall]

@mwegener So it is definitely my measure, as when I removed it data was populated. How can I make it more efficient to run only on the filtered items?

CountDaysOOS2.1 = CALCULATE(
                    COUNTROWS('ns vwInventorySnapshot'),
                                REMOVEFILTERS(Dimtime[Date]),
                                FILTER(
                                    'ns vwInventorySnapshot',
                                    'ns vwInventorySnapshot'[DetereminedQuantity] = 0
                                    && 'ns vwInventorySnapshot'[Date] >= if(isblank(MAX(MIN('ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock]),MIN(Dimtime[Date]))),TODAY(),MAX(MIN('ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock]),MIN(Dimtime[Date])))
                                    && 'ns vwInventorySnapshot'[Date] <=if(isblank(MIN(MAX('ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate]),MAX(Dimtime[Date]))),TODAY(),MIN(MAX('ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate]),MAX(Dimtime[Date])))
                                    )
                                    )

[mwegener]

Hi @NBOnecall 

could you post a model view screenshot?

[NBOnecall]

@mwegenerHere you go.

[mwegener]

Which columns do you show in the visual?

[NBOnecall]

Currently trying to show InternalId, LocationId, LocationName, Average of SalesAmount, Average of Rolling 30 Days, and CountDaysOOS2.1 (measure).

[mwegener]

Hi @NBOnecall ,

it's really hard, because i have no sample data to test.
But try this.

CountDaysOOS2.1 =
CALCULATE (
    COUNTROWS ( 'ns vwInventorySnapshot' ),
    REMOVEFILTERS ( Dimtime[Date] ),
    FILTER (
        FILTER (
            'ns vwInventorySnapshot',
            'ns vwInventorySnapshot'[DetereminedQuantity] = 0
        ),
        'ns vwInventorySnapshot'[Date]
            >= IF (
                ISBLANK (
                    MAX (
                        MIN ( 'ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock] ),
                        MIN ( Dimtime[Date] )
                    )
                ),
                TODAY (),
                MAX (
                    MIN ( 'ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock] ),
                    MIN ( Dimtime[Date] )
                )
            )
            && 'ns vwInventorySnapshot'[Date]
                <= IF (
                    ISBLANK (
                        MIN (
                            MAX ( 'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate] ),
                            MAX ( Dimtime[Date] )
                        )
                    ),
                    TODAY (),
                    MIN (
                        MAX ( 'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate] ),
                        MAX ( Dimtime[Date] )
                    )
                )
    )
)

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[NBOnecall · Accepted solution]

I ended up doing the following and so far it is working, unless you can see anything that it could miss.

OutofStock = if(
                                    'ns vwInventorySnapshot'[DetereminedQuantity] = 0
                                    && 'ns vwInventorySnapshot'[Date] >= 'ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock]
                                    &&  or('ns vwInventorySnapshot'[Date]<= 'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate],'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate] = BLANK()),1,0)
                                    

[Anonymous]

Hi,

Could you please explain in detail the formula that you used and how do you used it?

In which table do you used it? Is a measure or a calculated column?

The columns that you used are part of the table or did you create it?

I making that questions in order to understand how to apply that to my case.

Thank you