Forum Discussion

dmkblesser's avatar
dmkblesser
Icon for Advocate II rankAdvocate II
1 year ago
Solved

Need Help: SUMX with filter exceeds the ROW limit

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.    ...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
dmkblesser
Icon for Advocate II rankAdvocate II
1 year ago

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.