very long time to calculate

[krzysztof]

Hello, I’m asking for help. The measure below takes a very long time to calculate. I have tried many optimization methods, but unfortunately, I can’t seem to do it well. Could I ask you for some tips on how to improve this?

xL4L Rev Units = 
VAR dmax = CALCULATE ( MAX ( 'DWH DimDate'[DateID] ) )
VAR dmin = CALCULATE ( MIN ( 'DWH DimDate'[DateID] ) )
VAR temp = CALCULATETABLE (
        SUMMARIZE (
            'DWH FactL4L',
            'DWH FactL4L'[StoreID],
            "measure ACT", [Units_SUM],
            "measure LY", [Units_LY]
        ),
        'DWH FactL4L'[L4L_Monthly] = TRUE (),
        'DWH FactL4L'[DimDate_ID] <= dmax,
        'DWH FactL4L'[DimDate_ID] >= dmin,
        ALL ( 'DWH DimDate' )    )
RETURN
        CALCULATE (
            DIVIDE(
                SUMX ( temp, [measure ACT] - [measure LY]) ,
                SUMX ( temp, [measure LY] ),
                BLANK()
            )

[v-jialongy-msft · Accepted solution]

Hi @krzysztof 

Has your problem been resolved? If so, could you mark the corresponding reply as the solution so that others with similar issues can benefit from it?

If not, please try the following modified Dax:

xL4L Rev Units =
VAR dmax = MAX ( 'DWH DimDate'[DateID] )
VAR dmin = MIN ( 'DWH DimDate'[DateID] )
VAR temp =
FILTER (
'DWH FactL4L',
'DWH FactL4L'[L4L_Monthly] = TRUE () &&
'DWH FactL4L'[DimDate_ID] <= dmax &&
'DWH FactL4L'[DimDate_ID] >= dmin
)
RETURN
DIVIDE(
SUMX ( temp, [Units_SUM] - [Units_LY] ),
SUMX ( temp, [Units_LY] ),
BLANK()
)

• Since 'dmin' and 'dmax' are calculated on 'DWH DimDate'[DateID] , applying them with ALL ('DWH DimDate') could be creating a heavy calculation. Instead, try using FILTER directly on 'DWH FactL4L'
• 'SUMMARIZE' can slow down performance, especially with large datasets. If possible, directly aggregate 'Units_SUM' and 'Units_LY' inside a 'SUMX' loop instead of creating the table 'temp'.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Selva-Salimi]

Hi @krzysztof 

you can try the following modification:

xL4L Rev Units = 
VAR dmax = CALCULATE ( MAX ( 'DWH DimDate'[DateID] ) , ALL ( 'DWH DimDate' )  )
VAR dmin = CALCULATE ( MIN ( 'DWH DimDate'[DateID] ),ALL ( 'DWH DimDate' )   )
VAR temp =  SUMMARIZE (
            filter ('DWH FactL4L',  'DWH FactL4L'[L4L_Monthly] = TRUE (),
        'DWH FactL4L'[DimDate_ID] <= dmax,
        'DWH FactL4L'[DimDate_ID] >= dmin) ,
            'DWH FactL4L'[StoreID],
            "measure ACT", [Units_SUM],
            "measure LY", [Units_LY]
        )
RETURN
            DIVIDE(
                SUMX ( temp, [measure ACT] - [measure LY]) ,
                SUMX ( temp, [measure LY] ),
                BLANK()
            )

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.