DAX Measure performance - cumulative totals

[smithc]

I'm still relatively new to DAX and I'm trying to create a cumulative total. I have managed to achieve this, but it's incredibly slow to the point of almost being unusable.

I know I'm probably making some obvious errors and the slowness is likely down to some kind of iteration issue, so I would really appreciate any assistance.

Here is a sample of the data:

Each DATE and Model has a single contract value.

Then for each level of the hierarchy there are a series of attributes. Eg.

Level 1 - System

Attributes - Demand, Retail

Level 2 - Product

Attributes - Construction, Inernal, Red, Interest, Fundamental, Status

and so on.

I have created a separate cumulative total for each level of the hierarchy and then used a switch in the chart to change between each one. The cumulative total needs to start from the first value in the date range, and it also needs to respond to any filters applied (e.g. DataSeriesKey in the sample data).

Essentially the calculation for Cumulative Total is (pseudo code)

(If SUM(Position) >0 THEN 1, If SUM(Position) <1 THEN -1) * Contract Value for that Day/Model

Here are the DAX measures I have created, this example shows the System cumulative total:

This get's the balance for a particular Date/Model and calculates whether the outcome should be 1 or -1

Derived Balance By System = 
 
 VAR Position =
    SUMX (
        SUMMARIZE (
            FactFiscalSummary,
            FactFiscalSummary[DATE],
            FactFiscalSummary[Model],
            "BalanceDiff", SUM ( FactPnLSummary[PositiveBalances_System] ) - SUM ( FactPnLSummary[NegativeBalances_System])

        ),
        VAR _BalanceDiff = [BalanceDiff]
        
        RETURN
            SWITCH(TRUE(),
                ISBLANK(_BalanceDiff),BLANK(),
                _BalanceDiff = 0, 0,
                _BalanceDiff < 0, -1,
                _BalanceDiff > 0, 1
                
            )
    )

RETURN
    Position

This calculates the max contract value for the day

Derived Max Contract Value = 

VAR ContVal =
    MAXX (
        SUMMARIZE (
            FactFiscalSummary,
            FactFiscalSummary[DATE],
            FactFiscalSummary[Model],
            "MaxContractVal", MAX ( FactFiscalSummary[ContractValue])
        ),
         [MaxContractVal]
    
)

RETURN
    ContVal

Finally this calculates the cumulative total:

CumulTotal = 
 
 VAR MaxDate = MAX ( 'DimDate'[Date] ) 

 VAR MinDate = MINX (ALLSELECTED(DimDate),DimDate[Date])

VAR Total = SUMX(
                CALCULATETABLE(
                    ALLSELECTED(DimDate[Date]),
                    DimDate[Date] <= MaxDate && DimDate[Date] >= MinDate
                ),
               FactFiscalSummary[Derived Balance By System] * FactFiscalSummary[Derived Max Contract Value]
)
            


RETURN 
	Total

As I said, this works and produces the correct values and behaviour, but it's extremely slow and sometimes takes over a minute to calculate. There are about 16 million rows in FactFiscalSummary and depending on how selective the attributes are, this takes much longer. For example, System only has 2 attributes, Product has 6 attributes and takes much longer, etc.

Also I have tried putting all of these into the same measure but I don't get the correct results, I can only achieve this by creating three separate measures.

Any pointers would be greatly appreciated!

Thanks

[m3tr01d]

hello @smithc ,
can you send a sample file where we can do some testing on our side?

[smithc]

Hi Robert

Many thanks for the link, looks like there are some useful suggestions in there so I will try a few things out. Will report back if I can make any improvements.

Thanks again

Chris

[v-robertq-msft]

OK, thanks for the response.

[v-robertq-msft]

Hi, 

For a data set of this magnitude, I think a one-minute delay is a relatively normal phenomenon in my opinion.

You may refer to this document for some improvements in the DAX formulas:

https://maqsoftware.com/expertise/powerbi/dax-best-practices

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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