Forum Discussion

nofan_munawar's avatar
nofan_munawar
Frequent Visitor
3 years ago
Solved

how to improve cumulative performance

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 is...
Need Help
  • nofan_munawar's avatar
    nofan_munawar
    3 years ago

    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

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    nofan_munawar
    Frequent Visitor
    3 years ago

    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