SUMMARAZE performance / early materialization

Question

Hi, I have a large fact table. I need to calculate a tricky measure. The first step I need to group this table by a certain set of attributes, excluding repetitions, and then find the sum over the resulting table. I use this measure in detailing cumulative dates by month.
An example of dax:

'📊Measure'[TEST_v1] =

VAR _min = SELECTEDVALUE('Dim date'[firstdate_of_month_key],99999999)
VAR _max = MAX('Dim date'[date_key])

VAR _tbl2 =
ADDCOLUMNS (
SUMMARIZE (
CALCULATETABLE( 'Test_balance', KEEPFILTERS('Test_balance'[date_key]>=_min&&'Test_balance'[date_key]<=_max),REMOVEFILTERS('Dim date')),
'bridge_dpd_addition'[dpd_group_company],
'Test_balance'[prolongation_num_cumulative],
'Test_balance'[amount_usd]
),
"cnt", CALCULATE(DISTINCTCOUNT('Test_balance'[credit_key] ),KEEPFILTERS('Test_balance'[date_key]>=_min&&'Test_balance'[date_key]<=_max),REMOVEFILTERS('Dim date'))
)

VAR _res = SUMX(_tbl2,[amount_usd]*[cnt])

RETURN
_res
-------------------------
or _v2 instead of a _tbl2 I can use

VAR _tbl1 = SUMMARIZE(
    CALCULATETABLE( 'Test_balance', KEEPFILTERS('Test_balance'[date_key]>=_min&&'Test_balance'[date_key]<=_max),REMOVEFILTERS('Test_balance')),
    'bridge_dpd_addition'[dpd_group_company],
    'Test_balance'[prolongation_num_cumulative],
    'Test_balance'[amount_usd],
    'Test_balance'[credit_key]
    )
VAR _res =   SUMX(_tbl1,[amount_usd])
But in both cases I have early materialization of the data. Is there any way to avoid this?

johnt75 · Answer

If you have a relationship from your date table to the fact table then there is no need to apply the date filters to your fact table and you could simplify the code to
TEST_v1 =
VAR _tbl2 =
    ADDCOLUMNS (
        SUMMARIZE (
            'Test_balance',
            'bridge_dpd_addition'[dpd_group_company],
            'Test_balance'[prolongation_num_cumulative],
            'Test_balance'[amount_usd]
        ),
        "cnt", CALCULATE ( DISTINCTCOUNT ( 'Test_balance'[credit_key] ) )
    )
VAR _res =
    SUMX ( _tbl2, [amount_usd] * [cnt] )
RETURN
    _res

tamerj1 · Answer

AlexOakman&nbsp;
You may also try
TEST_v1 =SUMX (VALUES ( 'Dim date'[firstdate_of_month_key] ),VAR _min = 'Dim date'[firstdate_of_month_key]VAR _max =CALCULATE ( MAX ( 'Dim date'[date_key] ) )VAR _tbl1 =FILTER (CALCULATETABLE ( 'Test_balance', ALL ( 'Test_balance' ) ),'Test_balance'[date_key] &gt;= _min&amp;&amp; 'Test_balance'[date_key] &lt;= _max)VAR _tbl2 =SUMMARIZE (_tbl1,'bridge_dpd_addition'[dpd_group_company],'Test_balance'[prolongation_num_cumulative],'Test_balance'[amount_usd],'Test_balance'[credit_key])VAR _res =SUMX ( _tbl2, [amount_usd] )RETURN_res)