Forum Discussion

Burtoninlondon's avatar
Burtoninlondon
Frequent Visitor
2 years ago

Help needed to optimize DAX query

Hello,   I am a relatively new user of PowerBI, PowerQuery, DAX, etc. and have developed my dashboard with the help of this community. I'm saying this to provide an indication of my experience when...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,Burtoninlondon 

I am glad to help you.  

According to your description, you want help needed to optimize DAX query?

 

If I understand you correctly, then you can refer to my solution. 

 

  1. You can remove unnecessary data or optimize your data structure to speed up queries. 

 

  1. Regarding your two DAX formulas, I have optimized them as follows: 

 

Running Total =
VAR RunningTotal =
    SUMX (
        FILTER (
            ALL ( 'Strategy data' ),
            'Strategy data'[Date] <= MAX ( 'Strategy data'[Date] )
        ),
        'Strategy data'[Profit]
    )
RETURN
    RunningTotal


 

Max Drawdown =
VAR RunningTotal = [Running Total]
VAR RollingMax =
    CALCULATE (
        MAXX ( VALUES ( 'Strategy data'[Date] ), [Running Total] ),
        FILTER (
            ALL ( 'Strategy data'[Date] ),
            'Strategy data'[Date] <= MAX ( 'Strategy data'[Date] )
        )
    )
RETURN
    RunningTotal - RollingMax

 

You can refer to the official documentation to optimize your DAX:DAX function reference - DAX | Microsoft Learn

 

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.


Best Regards,
Fen Ling,


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