very long time to calculate

DAX DAX Calculations
1

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()
            )
        )
2

Hi @Harris - Can try Data Mentor https://mentor.enterprisedna.co/ for tips to optimize this.

Thanks
Ankit J

3

Hallo @Harris

Without having the data / some sample data, a few initial questions appear to my mind:

  1. why do you need the Summarize function, is aggregation not possible in the visual ?
  2. in the Summarize, only [StoreID] is added, have you tried to visualise your temp-table as a calculated table to see the prelimanary outcome, to see if it does what you expect ? What is the outcome in the calculated table of “measure ACT” and “measure LY” ? Assuming only a short table with all the stores with two columns with unit-quantities will appear
  3. why is DWH FactL4L[Units] not included in the Summarize? If the Summarize is needed, standaard sum- and Last Year-calculations should be possible with summarized DWH FactL4L[Units]
  4. why is a date-filter needed on the summarized DWH FactL4L table, selecting the DimDate_ID bigger than the minimum date in the table and smaller than the maximum date of the table ?
  5. why is SUMX needed in your DIVIDE formula ?
  6. Could BLANK() in your Divide formula not being sufficient if last year data is NOT available, and therefore ‘DWH FactL4L’[L4L_Monthly] = TRUE () is NOT needed as a filter at all (guessing this condition has to do with this) ?
  7. How are the measures [measure ACT] and [measure LY] calculated ?

Kind regards, Deltaselect