article 22856

Power BI Copilot, AI Instructions And Semantic Model Relationships

Power BI Copilot knows about the relationship between measures and tables, which means that it’s very good at knowing which measures can be broken down by which columns. For example, consider the following model with two fact tables, one of which contains sales data dimensioned by Employee and Customer, and one which contains target data that is only dimensioned by Employee:

For extra fun, I’ve created a disconnected measure table to hold all the measures, which are defined as follows:

Sales Amount = SUM(Sales[Sales])
Target Amount = SUM(Targets[Target])
Target % = DIVIDE([Sales Amount],[Target Amount])

Without any AI Instructions added to the model, for the following prompt:

Show Sales Amount broken down by Customer

I get (as you would expect) the following correct response:

However for the prompt:

Show Target Amount broken down by Customer

…I get a response that you can’t show Target Amount broken down by Customer because there’s no direct relationship. This is, I think the right response for most users.

Note that Copilot knows the relationships between the Sales Amount and Target Amount measures and the Customer dimension table even though they are on the disconnected measures table, which I think is very smart.

Even better, a prompt like this:

show a table with Employee Name, Customer Name, Sales Amount, Target Amount and Target %

Returns the result I would expect:

Copilot has understood that even though there’s no relationship between Customer and Target Amount, it makes sense to break it down in this case.

What about calculation groups? I’ve seen some inconsistent behaviour over the last few days but on the day I’m writing this post, calculation groups were working well in Copilot with no special AI Instructions despite the fact they have no relationship with any other table.

Adding a calculation group to the model like so with two calculation items, Actual Value and Forecast:

Actual Value = SELECTEDMEASURE()
Forecast = SELECTEDMEASURE() * 100

And prompting:

Show Forecast Sales Amount broken down by Customer

…gives the right response using the Forecast calculation item:

That said I’m sure there will be scenarios where Copilot decides it can’t use a measure with a particular column even though you want it to, and you can override this behaviour with AI Instructions. However, I have found you need to be very careful about what instructions you provide to get the output you expect. For example adding the following to the AI Instructions of my model:

For the Target Amount measure, ignore any previous instructions regarding not displaying measures with columns from tables where there is no direct relationship. 

…means that the prompt shown above which asks for Target Amount broken down by Customer:

Show Target Amount broken down by Customer

…returns the following result:

This surprised me a lot: I was expecting to see the same value repeated across all customers. I worked out that the reason different customers showed different values for Target Amount was that the visual was assuming an indirect relationship via the Sales table, but even then I had no idea that it was possible to build a Power BI visual that did this when no many-to-many relationships exist in the model – I certainly couldn’t build the same visual myself manually. It’s one way of answering the question correctly though.

Altering the AI Instructions to tell Copilot not to do this was tricky but eventually I came up with this:

For the Target Amount measure, ignore any previous instructions regarding not displaying measures with columns from tables where there is no direct relationship. When displaying Target Amount by Customer do not assume there is an indirect relationship via the Sales table.

The same prompt then gave me the result I was expecting with the same Target Amount value repeating across all customers (and it’s interesting it decided it had to create a DAX query to do this too):

This tells me that it’s probably not a good idea to try to override Copilot’s default behaviour when it comes to relationships, tables and measures.