Data and AI Training

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 (2025) 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.

superstore data model

  1. Launch Power BI Desktop and ensure that you have a recent version, preferably January 2025 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.

Here is a description of the data model that you can copy/paste as part of the initial LLM prompt.

A Power BI data model has several tables arranged in a star schema pattern.
The fact table, Transaction, has columns: 
* Transaction ID (unique)
* Order Date
* Ship Date 
* Customer ID  - joined to Customer[Customer ID]
* Product ID - joined to Product[Product ID]
* Postal Code - joined to Geography[Postal Code]
* Sales
* Profit
* Quantity
* Discount

The dimension tables are Customer, Product and Geography
Customer has columns Customer Name, Segment and Customer ID (unique). 
Product has columns Category, Sub-Category, Product Name and Product ID (unique)
Geography has columns Region, State, City and  Postal Code (unique)

The dates table, Dates, has the following columns: 
Date, Month, Quarter, Year, Year Quarter and Year Month, Day Of Week, Financial Year and Financial Quarter.  
The Financial Year starts in April.  
Each text column has a corresponding numeric column to sort the text column in chronological order. 
e.g., the Month column, with values "Jan", "Feb" etc has a corresponding Month Key column with values 1, 2, etc.

The dates table has two relationships with the Transaction table
* an active relationship between Dates[Date] and Transaction[Order Date]
* an inactive relationship between Dates[Date] and Transaction[Ship Date]

There is also a measures table, named Calculations, with several measures.  These are defined as follows

    MEASURE 'Calculations'[Sales Ordered] = SUM('Transaction'[Sales])
    MEASURE 'Calculations'[Number of Transactions] = COUNTROWS('Transaction')
    MEASURE 'Calculations'[Profit] = SUM('Transaction'[Profit])
    MEASURE 'Calculations'[Quantity] = SUM('Transaction'[Quantity])
    MEASURE 'Calculations'[Sales Shipped] = CALCULATE(
			[Sales Ordered],
			USERELATIONSHIP(
				Dates[Date],
				'Transaction'[Ship Date]
			)
		)

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.