Writing DAX with ChatGPT-4: A Comprehensive Review and Tutorial

In the ever-evolving landscape of business intelligence tools, Power BI and DAX (Data Analysis Expressions) continue to be powerful assets for data professionals. With the release of ChatGPT-4, the potential to streamline and enhance the process of writing DAX has become an exciting prospect. This article provides an in-depth look at how ChatGPT-4 can be utilized for writing DAX, drawing from a recent hands-on demonstration.

Setting the Stage

Recently, OpenAI released ChatGPT-4o, an upgraded version of their conversational AI. To explore its capabilities, a session was conducted to see how well it could assist in writing DAX code. The focus was on practical applications, from simple time intelligence measures to more complex calculations involving customer data and open orders.

Getting Started with Basic Measures

The session began with fundamental DAX measures such as sales amount, margin, and total cost. The first challenge posed to ChatGPT-4 was to calculate the year-to-date (YTD) sales amount. Despite some initial confusion, where ChatGPT-4 did not recognize the measure immediately, it was able to generate a correct DAX formula once the context was clarified:

Total Sales YTD = CALCULATE([Sales Amount], DATESYTD('Date'[Date]))

This demonstrated that ChatGPT-4 could handle basic DAX calculations with appropriate guidance.

Moving to Rolling Periods

The next task involved creating a rolling sum for the last six months, which is a bit more complex due to the need to dynamically adjust the date range. ChatGPT-4 provided a standard solution using the DATESINPERIOD function:

Rolling 6 Months Sales = CALCULATE([Sales Amount], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -6, MONTH))

This was a straightforward and accurate response, indicating that ChatGPT-4 can handle standard rolling period calculations effectively.

Challenges with Weekly Calculations

A significant challenge arose when attempting to calculate a rolling sum over the last 13 weeks. DAX does not natively support week-based calculations, which led to an incorrect suggestion from ChatGPT-4:

Rolling 13 Weeks Sales = CALCULATE([Sales Amount], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -13, WEEK))

This measure failed because DAX does not support "WEEK" as a parameter in DATESINPERIOD. After some troubleshooting, the correct approach was to use day-based calculations to approximate weeks:

Rolling 13 Weeks Sales = CALCULATE([Sales Amount], DATESBETWEEN('Date'[Date], MAX('Date'[Date]) - 91, MAX('Date'[Date])))

This highlighted a limitation in ChatGPT-4's understanding of DAX's function parameters.

Advanced Calculations: Returning Customers

For more advanced calculations, such as identifying returning customers, ChatGPT-4 initially struggled. The first attempt produced a convoluted and incorrect measure. However, after refining the request, a more accurate approach was provided:

Returning Customers = CALCULATE([Customer Count], FILTER(ALL('Sales'), 'Sales'[Order Date] < MAX('Date'[Date]) && 'Sales'[Customer Key] IN VALUES('Sales'[Customer Key])))

This measure accurately captures returning customers by filtering sales data for customers with previous orders. It demonstrated that with precise instructions, ChatGPT-4 can generate complex DAX measures.

Calculating Open Orders

The final challenge was to calculate the number of open orders at the end of a period, defined as orders with a delivery date after the end of the period. ChatGPT-4's initial solution required optimization:

Open Orders = CALCULATE(COUNTROWS('Sales'), 'Sales'[Order Date] <= MAX('Date'[Date]) && ('Sales'[Delivery Date] > MAX('Date'[Date]) || ISBLANK('Sales'[Delivery Date])))

While functionally correct, this measure was not optimal for performance. The optimized version, using column filters, is more efficient:

Open Orders = CALCULATE(DISTINCTCOUNT('Sales'[Order ID]), 'Sales'[Order Date] <= MAX('Date'[Date]) && ('Sales'[Delivery Date] > MAX('Date'[Date]) || ISBLANK('Sales'[Delivery Date])))

This refined measure ensures faster performance, especially in larger datasets.

Conclusion: The Power and Pitfalls of ChatGPT-4

ChatGPT-4 shows great promise in assisting with DAX code generation, offering speed and some degree of accuracy. However, it also highlights the importance of human oversight. Errors and inefficiencies can arise, particularly with more complex or unconventional calculations.

For those new to DAX, ChatGPT-4 can be a helpful learning tool, providing ideas and basic formulas. For experienced users, it serves as a supplementary tool to speed up the coding process, though careful validation and optimization are necessary.

As with any AI tool, the key is understanding its limitations and leveraging its strengths to enhance productivity and accuracy in DAX development. The journey of integrating AI into DAX writing is just beginning, and with continuous improvements, the future holds even greater potential.

Reference: SQLBI