Help needed to optimize DAX query

[Burtoninlondon]

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 you read this post.

My dashboard is simple and I intend to use it to visualise the performance of a portfolio of automated strategies trading on the US stock market. The data set comprises 17 individual excel files which I've combined in PowerQuery to form a single table in the PowerBI model, there is approximately 6,800 rows in this master table.

The dashboard works well and suits my needs however there is one DAX query which returns a "Resources Exceeded" error. To get this DAX query to run without an error I need to increase the memory in the 'Query-limit simulations' Report settings option to 16GB however the time taken to run this DAX query is long. The image below is from the PowerBI Performance Analyzer.

The query I have the problem with is called Max. Drawdown which I have established as a measure in my powerBI model. The DAX formula is as below:

Max Drawdown = 
MINX(
    VALUES('Strategy data'[Date]),
    VAR _p =
        WINDOW(1, ABS, 0, REL, ORDERBY( 'Strategy data'[Date] ) )
    RETURN
        [Running Total] - MAXX( _p, [Running Total] )
)

It refers to another measure called 'Running Total' which calculates the cumulative total by time.

Running Total = 
CALCULATE (
    SUM('Strategy data'[Profit]),
    FILTER ( ALLSELECTED('Strategy data'), 'Strategy data'[Date] <= MAX ( 'Strategy data'[Date] ) )
)

I have a date slicer on my PowerBI dashboard which I can use to filter the data by date and I also have a Strategy slicer which I can use to filter by each of my automated trading strategies.

I am reaching out to the community for support on how I can optimize this DAX query. Is there a more efficient way to structure the calculation? Can the calculation be performed closer to the data source (noting that I need the functionality of the slicers on the Power BI dashboard)? Or is there an alternative way to achieve what I need?

I have uploaded my PowerBI dashboard to wetransfer should you want to view the file...PBIX file 

Any help you can provide will be appreciated.

Neil

[Anonymous]

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. 

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