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 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

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

     

     

4 Replies

  • 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

       

       

  • nofan_munawar's avatar
    nofan_munawar
    Frequent Visitor
    3 years ago

    hi Anonymous , 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

     

  • nofan_munawar's avatar
    nofan_munawar
    Frequent Visitor
    3 years ago

    Anonymous 

     

    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