Power BI Copilot, AI Instructions And DAX Query Templates

At the end of my last post I showed how, if you put the definition of a measure in the AI Instructions of your Power BI semantic model, Copilot can use it as a template to create new measures with slightly different logic. That got me wondering – can you do the same thing for DAX queries to solve even more complex problems? It turns out you can.

First of all, why would you want to do this? There are some problems that can be solved relatively easily by writing a DAX query but which are quite hard to solve if you’re limited to writing measures. ABC classification is one example of this. Marco and Alberto have a great article on this problem here; the problem is when you want to increase the number of buckets/clusters/groups you use or change the rules for which transactions go into which buckets and make all this dynamic for end users building reports in Power BI Desktop then your semantic model can get very complicated very quickly. In a DAX query you can solve these problems much more easily, in part because you can define a calculated column at query time and use it to do your grouping. And since Copilot can generate and run DAX queries, it can be taught how to do ABC analysis by giving it an example DAX query and some instructions telling it how the query works. Let’s see an example.

The sample semantic model I’ve been using in this series has a fact table called Transactions where each row represents a real estate property sale in England and Wales. Let’s say you want to group these rows into different groups based on the price paid for the property. With nothing in the AI Instructions for the model, the following prompt:

Do an ABC analysis on the Transactions table, grouping all transactions into three groups named A, B and C based on the Price column where the upper boundary for the first group is £250000 and the upper boundary for the second group is £700000. Filter the transactions to just 1st January 2025.

…will sometimes generate the right result but will also sometimes generate a slightly strange or even incorrect result. Here’s an example where the information in the summary is correct but the DAX query is far from ideal:

EVALUATE
  // Create a table with transactions on 1st January 2025 and assign an ABC group based on Price
  SELECTCOLUMNS(
    FILTER(
      'Transactions',
      'Transactions'[Date] = DATE(2025, 1, 1)  // Filter transactions to 1st January 2025
    ),
    "Price", 'Transactions'[Price],
    "ABC Group", 
      SWITCH(
        TRUE(),
        'Transactions'[Price] <= 250000, "A",      // Group A: Price up to £250,000
        'Transactions'[Price] <= 700000, "B",      // Group B: Price over £250,000 and up to £700,000
        "C"                                       // Group C: Price above £700,000
      )
  )
ORDER BY
  [ABC Group] ASC,
  [Price] ASC

This query returns one row per transaction rather than one row per group:

Not something you can give to an end user for sure. Here’s a DAX query that gives me exactly the result I want:

DEFINE
//Create a new calculated column at query time 
//to create the groups for ABC classification
COLUMN 'Transactions'[Group] =
//Upper boundary for price for group A
VAR AUpperBoundary = 250000
//Upper boundary for price for group B
VAR BUpperBoundary = 700000
RETURN
//Return a different letter representing a group name
//based on where the value in the Price column sits in
//the boundaries defined
SWITCH(
TRUE(),
//If the price is less than or equal to the variable AUpperBoundary
//then return the value "A"
Transactions[Price]<=AUpperBoundary, "A (<=£250,000)",
//If the price is less than or equal to the variable BUpperBoundary
//then return the value "B"
Transactions[Price]<=BUpperBoundary, "B (>£250,000 and <=£700,000)",
//Otherwise return the value "C"
"C (>£700,000)"
)
//Returns the results of the classification
EVALUATE
SUMMARIZECOLUMNS(
    'Transactions'[Group],
    //Filter by a given date
     KEEPFILTERS( TREATAS( {DATE(2025,1,1)}, 'Date'[Date] )),
    "Count Of Transactions", [Count Of Transactions]
)
ORDER BY 
    'Transactions'[Group] ASC

Here’s what this query returns in DAX Studio:

Putting this query in the semantic model’s AI Instructions with some explanatory text, like so:

The following DAX query does an ABC analysis on the Transactions table, grouping transactions into three groups called A, B and C, for the 1st January 2025.  The transactions with the lowest prices always go into group A, then subsequent letters represent higher price ranges. If the user asks for an ABC analysis use this query as a template.

DEFINE
//Create a new calculated column at query time 
//to create the groups for ABC classification
COLUMN 'Transactions'[Group] =
//Upper boundary for price for group A
VAR AUpperBoundary = 250000
//Upper boundary for price for group B
VAR BUpperBoundary = 700000
RETURN
//Return a different letter representing a group name
//based on where the value in the Price column sits in
//the boundaries defined
SWITCH(
TRUE(),
//If the price is less than or equal to the variable AUpperBoundary
//then return the value "A"
Transactions[Price]<=AUpperBoundary, "A (<=£250,000)",
//If the price is less than or equal to the variable BUpperBoundary
//then return the value "B"
Transactions[Price]<=BUpperBoundary, "B (>£250,000 and <=£700,000)",
//Otherwise return the value "C"
"C (>£700,000)"
)
//Returns the results of the classification
EVALUATE
SUMMARIZECOLUMNS(
    'Transactions'[Group],
    //Filter by a given date
     KEEPFILTERS( TREATAS( {DATE(2025,1,1)}, 'Date'[Date] )),
    "Count Of Transactions", [Count Of Transactions]
)
ORDER BY 
    'Transactions'[Group] ASC

Means that the prompt above not only returns what you would expect:

…but more importantly, variations on the prompt such as:

Do an ABC analysis on the Transactions table, grouping all transactions into four groups named A, B, C and D based on the Price column where the upper boundary for the first group is £300000, the upper boundary for the second group is £750000 and the upper boundary for the third group is £900000. Filter the transactions to just 16th January 2025.

…take the template query and modify it as you would expect:

Note that the prompt has not only changed the date to filter by but increased the number of groups from three to four, and that Copilot has modified the DAX query to handle this – which I think is very impressive. Here’s the DAX query Copilot generated:

// DAX query generated by Fabric Copilot with "Group all transactions from the Transactions table into four groups named A, B, C, and D based on the Price column, where group A includes transactions with Price up to £300,000, group B includes transactions with Price above £300,000 and up to £750,000, group C includes transactions with Price above £750,000 and up to £900,000, and group D includes transactions with Price above £900,000. Filter the transactions to just 16th January 2025. Show the count of transactions in each group."
DEFINE
// Create a new calculated column at query time 
// to classify transactions into groups A, B, C, and D based on the Price column
COLUMN 'Transactions'[Group] =
    // Define the upper boundaries for each group
    VAR AUpperBoundary = 300000
    VAR BUpperBoundary = 750000
    VAR CUpperBoundary = 900000
    RETURN
        SWITCH(
            TRUE(),
            'Transactions'[Price] <= AUpperBoundary, "A (<=£300,000)",
            'Transactions'[Price] <= BUpperBoundary, "B (>£300,000 and <=£750,000)",
            'Transactions'[Price] <= CUpperBoundary, "C (>£750,000 and <=£900,000)",
            "D (>£900,000)"
        )
        
EVALUATE
    SUMMARIZECOLUMNS(
        'Transactions'[Group],
        // Filter transactions to include only the 16th January 2025
        KEEPFILTERS( TREATAS( { DATE(2025,1,16) }, 'Date'[Date] ) ),
        "Count Of Transactions", [Count Of Transactions]
    )
ORDER BY 
    'Transactions'[Group] ASC

Tools that convert natural language to SQL, like Fabric Data Agents, all allow you to specify example SQL queries like this so it’s hardly a surprise that the same approach applies to DAX. What’s interesting is the type of problems that giving Copilot templates of DAX measures and queries allows you to solve, and I’m only just starting to think of the possibilities.