Data Training Courses

Home | Power BI | Excel | Python | SQL | Generative AI | Visualising Data | Analysing Data

Use AI Tools for DAX

DAX is the data modelling and calculation language of Power BI. We can use an AI tool, together with the DAX query view in Power BI Desktop, to help us write our DAX queries and measures. This can also be a great learning exercise.

Exercise: Generate DAX queries against a Power BI data model

For this exercise, attendees must have a recent (2024) version of Power BI Desktop installed on their PC. This exercise assumes that attendees are familiar with Power BI Desktop and have a basic knowledge of DAX.

Note that Power BI tenants with a certain type of capacity license, can use Copilot as a code assistant directly from within the query view. However, in this exercise we will use an approach of using an external AI tool. That means that we need to copy / paste the DAX generated by the tool into the DAX query view but this is a small inconvenience.

Getting started with this exercise

In this exercise, we ask the AI tool to write DAX queries and measures based on a Power BI Data model that uses fictional data and has a standard star schema data model. This is the model view.

<img src=”https://zomalextrainingstorage.blob.core.windows.net/datasets/misc/Power%20BI%20Superstore%20Data%20Model.png” width=”600” alt=data model”>

  1. Launch Power BI Desktop and ensure that you have a recent version, preferably January 2024 or later.
  2. In Preview features, enable DAX Query View (if it is still a Preview feature in your version)
  3. Download this Power BI file and open it in Power BI Desktop.
  4. The Power BI file contains a standard star schema data model.Review the data model, the table and column names, relationships etc. Consider what DAX measures you would add to this model.

Ask the AI to generate DAX queries and define measures

Here are a set of prompts you can use to ask the AI to generate DAX queries. Use these as a starting point. You may want to copy the response to each prompt into a new tab in the query view and add the initial prompt as a comment

The initial prompt refers to the image of a data model, shown above. You can download it from here

Prompts:

Act as an expert in writing DAX queries. I will provide you with an image of a Power BI data model then ask you to write queries based on that model.

Write a query to show Sales by Region

Rephrase the query for use in the DAX query view. Start the query with EVALUATE and use SUMMARIZECOLUMNS rather than SUMMARIZE.

Enclose all table names in single quotes in future code examples.

Rephrase the query to define the Total Sales measure before the EVALUATE.

The data model now has a table named Calcs to hold all our measures. Rephrase the query above so that the Total Sales measure is in the Calcs table.

Please write query to define the measure as ‘Sales’ rather than ‘Total Sales’

Write a query to define Profit and Quantity measures in a similar way

Assume now that I have added the Sales, Quantity and Profit measures to the data model. Write a query to define then use a measure named COGS. The calculation is Sales - Profit

Write a query to define a measure ‘Number of Transactions’

There is a shared ChatGPT conversation based on this example exercise.
here. You will need to be signed in to ChatGPT to view it.