Need Help: SUMX with filter exceeds the ROW limit

[dmkblesser]

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!

[dmkblesser]

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. 

[AlexisOlson]

@v-agajavelly 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.)

[dmkblesser]

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. 

[v-agajavelly]

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.

[dmkblesser]

Hi @v-agajavelly , 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. 

[johnt75]

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.

[dmkblesser]

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 

[johnt75 · Accepted solution]

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.

[AlexisOlson]

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]
    )
)

[AlexisOlson]

Try this instead:

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

[dmkblesser]

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]}

[dmkblesser]

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

[FBergamaschi]

Can you show the error message in detail?

Sounds like you are in direct query, are you?

[dmkblesser]

Hi @FBergamaschi , the error message is: 

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

[FBergamaschi]

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

[dmkblesser]

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