This was a big week for Power BI Copilot with the announcement of a new direction for the feature. If you haven’t done so already, read the two announcement blog posts here and here and check out this demo video. There are some pros and cons to this change (yes, you’re going to need an M365 Copilot Premium licence to use it but no Fabric capacity is needed any more – this works with models in Pro workspaces too) and there are more features still to come and problems to be solved, but the bottom line is that all of the work you’ve done preparing your semantic models for Copilot is not wasted and while the existing Power BI Copilot worked pretty well, the combination of M365 Copilot and Power BI semantic models works much, much better.

Probably the biggest change is that whereas the existing Power BI Copilot can answer questions in a number of different ways, for example by constructing a Power BI visual, M365 Copilot usually just generates and runs DAX queries against the underlying semantic model. It’s very good at generating syntactically correct DAX queries and of course your model’s AI Instructions are respected, but two risks remain:

1. M365 Copilot generates a DAX query that returns the correct result but not necessarily one that is optimal in terms of performance or CU cost
2. Multiple end users want to do the same kind of analysis but don’t know precisely how it should be performed, give vague instructions or are inconsistent, so they might each get slightly different results

Both of these problems can be solved in different ways but as a semantic model developer I find that adding DAX User-Defined Functions (UDFs) to your model is the easiest way to do so. This is something I wrote about last year but I thought I would revisit the topic because of its importance in the new world of M365 Copilot. If you’re going to centralise all your business logic in a semantic model then that should include the definition of complex analytical operations, something that a DAX UDF that returns a table can handle better than a DAX fragment embedded in your AI Instructions or a text description of how your analysis should be performed.

Using the same semantic model containing UK Land Registry Price Paid data and the same ABC analysis UDF from that previous post, I ran a similar test to the one in that post in M365 Copilot. I only made one change to the AI Instructions for the semantic model, adding two new paragraphs highlighted below:

This semantic model contains a DAX user-defined function called ABC that does an ABC analysis on the data in the Transactions table. It takes three parameters defined as follows:
AUpperBoundary - an integer value which is the upper boundary of transactions in the A group
BUpperBoundary - an integer value which is the upper boundary of transactions in the B group
AnalysisDate: a datetime value which is the date to filter transactions on
The function returns a table which can be used in an EVALUATE statement in a DAX query.

If a user asks for an ABC analysis then you must **always** use this function. Do not add any other columns or totals or try to do any other calculations on what this function returns, just return the table that the function returns and nothing else.

If the user does not specify the values needed for the function parameters please ask the user for them - do not make any assumptions. Never try to generate the DAX for an ABC analysis yourself because it may result in incorrect or inconsistent results.

For example if I wanted to see the number of transactions which took place on 1st January 2025 divided into three groups:
A - transactions between £0 up to and including £250000
B - transactions above £250000 up to and including £700000
C - transactions above £700000
I could call the function as follows:
ABC(250000, 700000, DATE(2025,1,1))

I added the first new paragraph because I found M365 Copilot was trying to be too clever and adding extra columns and subtotals that I didn’t think were necessary, and running extra DAX queries in order to do so. I added the second one when I found that if I asked a vague question then Copilot made too many assumptions and didn’t use my UDF.

The following prompt:

Use this semantic model <insert URL of semantic model here> to do an ABC analysis. The upper boundary for the first group is £290000 and the upper boundary for the second group is £790000. Filter the transactions to just 20th January 2025.

…resulted in the following output:

…which is exactly what I wanted. What’s more it worked consistently across all my tests and I could see that the only DAX queries run looked like this:

EVALUATE
ABC(290000, 790000, DATE(2025,1,20))
ORDER BY [ABC Group]

When I tried a vaguer prompt, like:

Use this semantic model <insert URL of semantic model here> to do an ABC analysis.

M365 Copilot asked me for the extra information it needed to do the ABC analysis:

Interestingly when I deleted the AI Instructions above and pasted more or less the same text into the Description property of the UDF, I found that M365 Copilot did not use the UDF and instead it generated its own DAX query – which still gave the correct result but which included a number of additional measures from the semantic model that I didn’t think were relevant. So explaining in your AI Instructions what UDFs are present and what they should be used for is necessary.

Anyway, to reiterate my point, I think DAX UDFs are going to be more and more important in the future. Up to now they’ve been useful as a way of encapsulating and reusing logic across calculations inside a semantic model; Power BI, alas, doesn’t provide a way for a report to show the output of a table-valued UDF. As more users consume data from Power BI semantic models outside Power BI reports, in tools like M365 Copilot that can answer questions by generating any DAX query they want, then DAX UDFs provide a way for semantic model developers to specify how certain types of analysis should be performed to ensure consistency and performance.

Can you call a DAX UDF from Power BI Copilot? I was asked this question by Jake Duddy during the livestream on Power BI Copilot I did with Reid Havens last week. I already knew it was possible because one of the customers I work with had already tried it, but I hadn’t tried it myself. So I did, and it is possible, and here’s the blog post.

A few months ago I wrote a post about how you can put template DAX queries in your AI Instructions to show Copilot how to solve more complex problems that can only be solved with a custom DAX query. I took some of the code from that post and turned it into the following DAX UDF:

createOrReplace

	function ABC = ```
			(
				AUpperBoundary: SCALAR int64,
				BUpperBoundary: SCALAR int64,
				AnalysisDate: SCALAR datetime
			) => 
			VAR ApplyAnalysisDate = 
			CALCULATETABLE(
				'Transactions',
				'Date'[Date] = AnalysisDate
			)
			VAR AddGroupColumn = 
			ADDCOLUMNS(	
				ApplyAnalysisDate, 
				"Group",
				SWITCH(
					TRUE(),
					//If the price is less than or equal to AUpperBoundary
					//then return the value "A"
					Transactions[Price]<=AUpperBoundary, "A (<=£" & AUpperBoundary & ")",
					//If the price is less than or equal to BUpperBoundary
					//then return the value "B"
					Transactions[Price]<=BUpperBoundary, "B (>£" & AUpperBoundary & " and <=£" & BUpperBoundary & ")",
					//Otherwise return the value "C"
					"C (>£" & BUpperBoundary & ")"
				)
			)
			RETURN
			SUMMARIZE(
			    AddGroupColumn,
				[Group],
			    "Count Of Transactions", [Count Of Transactions]
			)

This UDF does a basic form of ABC analysis on the semantic model I’ve used in all my recent Copilot posts containing UK Land Registry data on real estate transactions:

Note: this is not great quality code and it’s certainly not a general purpose solution for ABC analysis in DAX, but it’s a UDF. I then added the following to my AI Instructions describing what the UDF does and how to use it:

This semantic model contains a DAX user-defined function called ABC that does an ABC analysis on the data in the Transactions table. It takes three parameters defined as follows:
AUpperBoundary - an integer value which is the upper boundary of transactions in the A group
BUpperBoundary - an integer value which is the upper boundary of transactions in the B group
AnalysisDate: a datetime value which is the date to filter transactions on
The function returns a table which can be used in an EVALUATE statement in a DAX query.

For example if I wanted to see the number of transactions which took place on 1st January 2025 divided into three groups:
A - transactions between £0 up to and including £250000
B - transactions above £250000 up to and including £700000
C - transactions above £700000
I could call the function as follows:
ABC(250000, 700000, DATE(2025,1,1))

Then I tried 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 £290000 and the upper boundary for the second group is £780000. Filter the transactions to just 14th January 2025.

Here’s the response I got:

As in that previous post, Copilot generated a DAX query to answer the question. Apart from that weird Sort Key column it’s the right answer; an additional AI Instruction telling Copilot to sort on the Group column might help here. The difference between this response and the one I got in my previous post is that this time the DAX query generated by Copilot simply calls the UDF. Here it is:

// DAX query generated by Fabric Copilot with "Perform an ABC analysis on the Transactions table, grouping all transactions into three groups named A, B, and C based on the Price column, where group A includes transactions with Price up to £290,000, group B includes transactions with Price above £290,000 and up to £780,000, and group C includes transactions with Price above £780,000. Only include transactions from 14th January 2025."
// ABC analysis for Transactions on 14-Jan-2025 with the specified price boundaries.
// A: Price <= 290,000
// B: 290,000 < Price <= 780,000
// C: Price > 780,000
// The ABC function is user-defined in the model and returns a table.
// Since the column names returned by ABC are not specified, add a dummy sort column to satisfy the ORDER BY requirement.
EVALUATE
  ADDCOLUMNS(
    ABC(290000, 780000, DATE(2025, 1, 14)),
    "Sort Key", 0
  )
ORDER BY
  [Sort Key] ASC

I guess the advantage of this over the DAX template approach is that it’s likely to be a lot more reliable and consistent: the less DAX Copilot has to generate, the more likely it is to do what you expect. On the other hand it’s a lot less flexible. For example I tried this prompt from my previous blog post which asks for an ABC analysis with four rather than three groups:

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.

I got the correct result from Copilot but the DAX query generated didn’t use the UDF because the UDF is hard coded to only return three groups; I suppose I was lucky in this case.

Based on these – admittedly fairly basic – tests I think using DAX UDFs with Power BI Copilot could be very useful when you need Copilot to generate complex measures or calculations where you know the general DAX pattern to use.