how to improve cumulative performance

[nofan_munawar]

hi everyone,

i have performance issue with cumulative, my idea is to move the logic from powerBI to sql, is it effective ? or any other suggestion ?

below my dax script which have performance issue

cumCustomerByBook =

var maxDate = calculate(MAX(SellingUnit[logDate]),SellingUnit[clusterCode_unit]in VALUES(ClusterMapping[clusterCode_unit]))

return

CALCULATE(SUM(SellingUnit[counting_unit]),

FILTER(

    ALL(SellingUnit),SellingUnit[logDate]<= maxDate &&

                    SellingUnit[flag] = "Customer"  &&

                    SellingUnit[isLaunching] = 1 &&

                    SellingUnit[clusterCode_unit]

                        in VALUES(ClusterMapping[clusterCode_unit])

                    ))

thanks

[nofan_munawar]

@v-shex-msft 

i have an update sir,

one factor why the performance is not good is because im include year on date hierarchy

without year it takes 4 second, and with year it takes 1 minutes

[nofan_munawar]

hi @v-shex-msft , thanks for your fast response

9765 -> total row data

i want to compare cumulative value group by flag (customer & sales)
thats why i create 2 measure ( cumulative by customer, cumulative by sales )
which my previous excample is cumulative by customer -> 

SellingUnit[flag] = "Customer"

and also i want to filter by date and cluster ( it can be multiple filter ) -> thats by im using "in values"

SellingUnit[clusterCode_unit]
in VALUES(ClusterMapping[clusterCode_unit])

and function by default of cumulative im using maxdate

SellingUnit[logDate]<= maxDate

while im using your script the result is different

warm regards,

Nofan irkham

[v-shex-msft]

Hi @nofan_munawar,

How many records stored in data table that you calculated? Can you please share some mode detail about your scenario?

How to Get Your Question Answered Quickly  

It obviously will cause the performance issue when you use cumulative expression to calculate through huge amount of table records. You can also try to use the following formula if helps:

cumCustomerByBook =
VAR maxDate =
    CALCULATE (
        MAX ( SellingUnit[logDate] ),
        ALLSELECTED ( SellingUnit ),
        VALUES ( ClusterMapping[clusterCode_unit] )
    )
RETURN
    CALCULATE (
        SUM ( SellingUnit[counting_unit] ),
        FILTER (
            ALLSELECTED ( SellingUnit ),
            SellingUnit[logDate] <= maxDate
                && SellingUnit[flag] = "Customer"
                && SellingUnit[isLaunching] = 1
        ),
        VALUES ( ClusterMapping[clusterCode_unit] )
    )

Regards,

Xiaoxin Sheng

[nofan_munawar · Accepted solution]

finally i found the solution

oke, the issue is "slow performance while using cumulative"

root couse -> my chart using "dimdate" instead of "logdate"

solution -> for cumulative dont using relational table, use column that u call in measure

i change from dimDate to logdate and viola from 1 minutes to 15seconds