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.

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

Try this instead:

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

 

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

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

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

      Can you show the error message in detail?

       

      Sounds like you are in direct query, are you?

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

        Hi FBergamaschi , the error message is: 

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

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

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