Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX query taking long time to execute

Hi, I am working with DAX query and it is taking long time to execute in the Power BI. Below is the query which i am using, could you please suggest efficient way to achieve the same results and faster execution. Currently it is taking 169023(ms) which is too high as data size is not that big and only single calculation is happening on fly.

 

 

 

Cumulative_Return(in%) = 
var _maxIndex = Max(Query1[Index])
return
 CALCULATE(
     (exp(sum(Query1[LogReturn]))-1)*100,
     ALLSELECTED(Query1),
     VALUES(Query1[ProductID]), 
     Query1[Index] <= _maxIndex
     )

 

 

Need Help

2 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi GaneshAB100,

    I am not professional in performance, you could try below measure to see whether it work or not.

    Cumulative_Return(in%) = 
var _maxIndex = Max(Query1[Index])
return
 CALCULATE(
     (exp(sum(Query1[LogReturn]))-1)*100,
     ALLexcept(Query1,Query1[ProductID]), 
     Query1[Index] <= _maxIndex
     )

     In addition, you also could try to use Performance Analyzer to see whteher part cause more time. And you could refer to this link and Improve Power BI Performance by Optimizing DAX   for details.

    Best Regards,
    Zoe Zhi

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Zoe Zhi,

       

      Thanks for your reply. 

      I am using Performance Analyzer only, to track the performace for the components in dashboard and came to know that DAX is taking long time to execute. Also, the query you have shared is not working properly and giving different results than my original answers.