topic Re: Need Help: SUMX with filter exceeds the ROW limit in DAX Commands and Tips

Need Help: SUMX with filter exceeds the ROW limit

dmkblesser — Tue, 08 Jul 2025 16:02:26 GMT

Hi Community,

Here is my set up. I have a fact table connects to a date dimensional table. The fact table itself is quite large.

The source of the fact table is another semantic model.

Here are the measure I use:

CALCULATE(SUMX(FILTER(FactTable,FactTable[amount_base] < 0), FactTable[amount_base]), DATESBETWEEN(DimTable[business_date], [opening_date_range], [closing_date_range]))

CALCULATE(SUMX(FILTER(FactTable,FactTable[amount_base] > 0), FactTable[amount_base]), DATESBETWEEN(DimTable[business_date], [opening_date_range], [closing_date_range]))

This measure returns an error message of the resultset of a query to external data source has exceeded the maxium allowed size of '1000000'. I believe it is due to the filter to check the FactTable[amount_base] > 0 and FactTable[amount_base] < 0.

If I just calculate the sum

CALCULATE(SUM(FactTable[amount_base]), DATESBETWEEN(DimTable[business_date], [opening_date_range], [closing_date_range])), this is very fast and no error returns.

If I use the sum formula and apply the filter [amount_base] >0 on the visual level, it also returns the correct value and runs pretty faster. Unfortunately, I need to put all three measures (without filter, >0 and <0) on the same visual, so that I cannot just use visual level filter.

I have also tried to rewrite the measure to
CALCULATE(SUM(FactTable[amount_base]),FactTable[amount_base] < 0,DATESBETWEEN(DimTable[business_date], [opening_date_range], [closing_date_range])) but still no luck.

Any other tricks or tips I can utilize here? Appreciate your help!

Re: Need Help: SUMX with filter exceeds the ROW limit

AlexisOlson — Tue, 08 Jul 2025 16:29:55 GMT

Try this instead:

CALCULATE ( SUM ( FactTable[amount_base] ), KEEPFILTERS ( FactTable[amount_base] < 0 ), DATESBETWEEN ( DimTable[business_date], [opening_date_range], [closing_date_range] ) )

Re: Need Help: SUMX with filter exceeds the ROW limit

dmkblesser — Tue, 08 Jul 2025 16:59:05 GMT

Hi AlexisOlson , thanks for your help! unfortunately, I'm still receiving the same error.

Re: Need Help: SUMX with filter exceeds the ROW limit

dmkblesser — Tue, 08 Jul 2025 17:41:06 GMT

Hi AlexisOlson , with your suggestion and I found this video: https://www.youtube.com/watch?v=GFENAFw1co4 which talks about converting the visual filter into a measure. I think this is the same concept you suggested. I followed the same steps and received the same error. The interesting part is

DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('FactTable'[amount_base])), 'FactTable'[amount_base] < 0 ) VAR _result = CALCULATE([sum_no_filter], __DS0FilterTable) EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, "sum_no_filter", IGNORE('FactTable'[sum_no_filter]) )

the above part works, it does return me value. [sum_no_filter] here basically is the measure the sum measure in my original post:
CALCULATE(SUM(FactTable[amount_base]), DATESBETWEEN(DimTable[business_date], [opening_date_range], [closing_date_range])), this is very fast and no error returns.

However, this part fails when i tried to convert into an actual measure definition:

DEFINE MEASURE 'FactTable'[TEST] = VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('FactTable'[amount_base])), 'FactTable'[amount_base] > 0 ) VAR _result = CALCULATE([sum_no_filter], __DS0FilterTable) RETURN _result EVALUATE // SUMMARIZECOLUMNS( // __DS0FilterTable, // "sum_no_filter", IGNORE('FactTable'[sum_no_filter]) // ) {[TEST]}

Re: Need Help: SUMX with filter exceeds the ROW limit

FBergamaschi — Tue, 08 Jul 2025 20:52:58 GMT

Can you show the error message in detail?

Sounds like you are in direct query, are you?

Re: Need Help: SUMX with filter exceeds the ROW limit

dmkblesser — Tue, 08 Jul 2025 21:25:05 GMT

Hi FBergamaschi , the error message is:

yes, it is DQ to AS: connecting to another semantic model

Re: Need Help: SUMX with filter exceeds the ROW limit

FBergamaschi — Wed, 09 Jul 2025 07:39:53 GMT

OK is this error coming from Desktop or Cloud or both?

Re: Need Help: SUMX with filter exceeds the ROW limit

johnt75 — Wed, 09 Jul 2025 09:58:16 GMT

You could try

Sum less than 0 = VAR BaseTable = ADDCOLUMNS ( FILTER ( KEEPFILTERS ( VALUES ( 'FactTable'[amount_base] ) ), 'FactTable'[amount_base] > 0 ), "@num rows", CALCULATE ( COUNTROWS ( 'FactTable' ) ) ) VAR Result = SUMX ( BaseTable, 'FactTable'[amount_base] * [@num rows] ) RETURN Result

This may work depending on how many unique values you have in the amount base column.

Re: Need Help: SUMX with filter exceeds the ROW limit

Anonymous — Wed, 09 Jul 2025 10:00:04 GMT

Hi dmkblesser ,

Yes, your suggestion around pushing filters into the measure (instead of using SUMX(FILTER(...))) is absolutely valid and works well to avoid hitting the row limit issue in large datasets or live semantic models.

In my case, the issue was caused by this pattern.

Test = CALCULATE(SUMX(FILTER(FactTable, FactTable[amount_base] > 0),FactTable[amount_base]),DATESBETWEEN(...))

This was triggering the error. The resultset of a query to external data source has exceeded the maximum allowed size of '1000000', due to FILTER() attempting to materialize too many rows.

The approach that finally worked based on your advice was restructuring the measure like this.

PositiveAmount = CALCULATE([sum_no_filter], FactTable[amount_base] > 0)

sum_no_filter = CALCULATE(SUM(FactTable[amount_base]), DATESBETWEEN(DimTable[business_date], [opening_date_range], [closing_date_range]))

This pattern avoids SUMX and heavy row context, and keeps the model fast and responsive even with large datasets or live connections. I just needed to restructure the logic as standalone measures instead of trying to wrap everything in one definition.

Regards,
Akhil.

Re: Need Help: SUMX with filter exceeds the ROW limit

dmkblesser — Wed, 09 Jul 2025 15:14:02 GMT

hi FBergamaschi , I received from both end. Some additional info but may not help in this situation, on the service end, we are in F64

Re: Need Help: SUMX with filter exceeds the ROW limit

dmkblesser — Wed, 09 Jul 2025 15:20:41 GMT

Hi Anonymous , I'm not sure if you are providing a summary or a solution.

1. it did work with pushing the filter to the visual but it is not a solution since what if you need to add multiple measures to one visual? the visual filter may contradicts different measures and return blank value for the visual.

2. the resturcted measure does not work.

Re: Need Help: SUMX with filter exceeds the ROW limit

AlexisOlson — Wed, 09 Jul 2025 17:35:56 GMT

Anonymous appears to be copy and pasting AI-generated content.

The problem here might be that the formula engine is converting the filter condition FactTable[amount_base] < 0 into list of all negative amounts and applying that list as a filter. If there are more than 1 million distinct negative amounts, this causes the row limit problem.

The goal is to find another way of doing this that doesn't materialize a large filter table like that but it's not easy to control how the optimizer decides to do things.

Maybe try writing without a CALCULATE for this case and hope the optimizer can figure out how to run this without materializing the FILTER table.

VAR _Open = [opening_date_range] VAR _Close = [closing_date_range] VAR _Result = SUMX ( FILTER ( FactTable, FactTable[amount_base] < 0 && FactTable[date_col] >= _Open && FactTable[date_col] <= _Close ), FactTable[amount_base] ) RETURN _Result

(You'll need to replace [date_col] with the appropirate name of the column your fact table.)

Re: Need Help: SUMX with filter exceeds the ROW limit

dmkblesser — Wed, 09 Jul 2025 20:18:53 GMT

Hi johnt75, that's a smart workaround. Thanks for sharing that. Can you help me understand it better? For testing purpose, I import my fact table and create a calculated table using the BaseTable formula you provided. It actually returns me over 5 mill rows. I'm supurised to find that this `Sum less than 0` formula actually be able to return a value in the end.

since my original formula has DATESBETWEEN(DimTable[business_date], [opening_date_range], [closing_date_range])), the [opening_date_range] is a measure which adds some additional complexity. the [opening_date_range] is actually = to the a value from a date slicer + 1 day.

Your solution worked perfectly with the open date slicer and closing date slicer but since the extra complexicity with [opening_date_range], the end result is missing by a day's value.

I tried to put that logic on top of the formula you provided and then I'm getting the same error again.

Do you know any way to fix that using Dax? From data model perspective, I can add a new column to be something like true_open_date. I appreciate your help on getting the foundamental part working. I will still accept as Solution

Re: Need Help: SUMX with filter exceeds the ROW limit

dmkblesser — Wed, 09 Jul 2025 20:22:21 GMT

thanks buddy, unfortately I'm still getting the same error.

The funny part is I'm basically converting a report using DQ connecting to the Data source (Databricks) to use DQ connecting to a semantic model with data imported. Both approaches have the same data, and both are using DQ connection. The original formula worked in the DQ to Databricks but doesnt work with DQ to AS.

Re: Need Help: SUMX with filter exceeds the ROW limit

AlexisOlson — Wed, 09 Jul 2025 21:50:25 GMT

If you can add a new column, you can define the SIGN( FactTable[AmountBase] ) as a new column and write

CALCULATE ( SUM ( FactTable[amount_base] ), FactTable[Sign] = -1, DATESBETWEEN ( DimTable[business_date], [opening_date_range], [closing_date_range] ) )

Re: Need Help: SUMX with filter exceeds the ROW limit

johnt75 — Thu, 10 Jul 2025 08:47:39 GMT

I think you can add the time intelligence logic like

Sum less than 0 = VAR BaseTable = CALCULATETABLE ( ADDCOLUMNS ( FILTER ( KEEPFILTERS ( VALUES ( 'FactTable'[amount_base] ) ), 'FactTable'[amount_base] > 0 ), "@num rows", CALCULATE ( COUNTROWS ( 'FactTable' ) ) ), DATESBETWEEN ( DimTable[business_date], [opening_date_range], [closing_date_range] ) ) VAR Result = SUMX ( BaseTable, 'FactTable'[amount_base] * [@num rows] ) RETURN Result

By wrapping the entire ADDCOLUMNS inside the CALCULATETABLE both the filtering and the count of the number of rows will be done in a filter context which is restricted to the relevant dates.

The thinking behind my approach was to limit the number of rows you have to iterate over to get the sum. By combining the value with the number of occurences you still get the right number but hopefully with fewer rows as there would be a smaller number of unique amounts. Given that it is still returning 5 million rows though that won't be enough.

As you are able to make changes to the model, I think that creating aggregation tables might be the best option, depending on how many dimensions you need to be able to slice by, and the cardinality of those dimensions. If you are just slicing by date then creating a snapshot of each day should be doable, to store the total amount < 0 and the total amount >0 for each day. Your measures would then simply sum this aggregation table.

For more on aggregation techniques you might find https://www.sqlbi.com/articles/optimizing-incremental-inventory-calculations-in-dax/ useful.

Re: Need Help: SUMX with filter exceeds the ROW limit

dmkblesser — Thu, 10 Jul 2025 17:01:39 GMT

thank you so much johnt75 and AlexisOlson. The ideas and the info you provided are very helpful and inspiring.

I would like to avoid making changes to the table schema and keep everything within a calculated measure as much as possible but I guess it just add too much overhead to make that work.