topic Re: SUMMARAZE performance / early materialization in DAX Commands and Tips

SUMMARAZE performance / early materialization

AlexOakman — Tue, 14 Mar 2023 12:31:23 GMT

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?

Re: SUMMARAZE performance / early materialization

johnt75 — Tue, 14 Mar 2023 15:36:11 GMT

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

Re: SUMMARAZE performance / early materialization

tamerj1 — Tue, 14 Mar 2023 17:45:05 GMT

AlexOakman

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] >= _min
&& 'Test_balance'[date_key] <= _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
)

Re: SUMMARAZE performance / early materialization

AlexOakman — Wed, 15 Mar 2023 06:57:54 GMT

I need to calculate cumulative values for the month by day. Something like that:

Re: SUMMARAZE performance / early materialization

AlexOakman — Wed, 15 Mar 2023 07:01:02 GMT

Thanks for the advice. Unfortunately the result is the same as for ADDCOLUMN(SUMMARAZE(...)) =(

Re: SUMMARAZE performance / early materialization

AlexOakman — Mon, 20 Mar 2023 14:13:20 GMT

Solved by creating additional columns with a sign 1/0 and a simple Calculate (sum(),flag=1)