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.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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

    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 

     

     

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      1 year ago

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

       

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      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.