Category: Copilot

What Can You Do With Copilot In DAX Query View?

On my LinkedIn feed this morning I saw an amazing demo from Brian Julius showing how you can use AI to generate code for Python visuals in Power BI, immediately followed by this video from Marco Russo warning us not to believe all the hype about AI. I guess I, like most people, am waiting to see what turns out to be useful in the real world as a result of all of the current investment in AI. Certainly every Power BI customer I talk to these days is very interested in Copilot and the focus always seems to be using Copilot to understand the data shown on a report or to ask questions about the data in a semantic model. That’s understandable because these are the features that will benefit the largest number of users. However, I think the often-overlooked Copilot in DAX Query View is where you’ll get the most value today.

I have to admit that I’m a relatively late convert to DAX Query View, probably because I’ve been a hardcore DAX Studio user for so long. The ease with which you can write, test and update the definitions of measures in your model with DAX Query View is what lured me over and once I was there I started testing its Copilot features too. The documentation gives you the basics of what is possible with Copilot in DAX Query View but it turns out it’s capable of a lot more. So what are the practical benefits of Copilot in DAX Query View?

Writing measures

DAX is incredibly powerful but it’s also hard to learn. Up until recently if you were struggling to write a DAX measure you’d probably go to your favourite search engine, search for blog posts or questions on forums that seemed to describe what you wanted to achieve, copy some code from them and hope you can adapt it to your own needs. Nowadays more and more people are using LLMs to write DAX and while this doesn’t replace the need to learn the language (don’t throw away that copy of “The Definitive Guide to DAX” yet) it is a faster way to get working code than using search engines alone. Copilot in DAX Query View gives you this experience more conveniently right inside Power BI Desktop and with none of the security and privacy concerns about using a free public AI service. It also knows about the structure of your semantic model without you needing to explicitly pass that information in through your prompts, leading to better quality results.

Let’s see an example. Consider the following model:

In DAX Query View, the following Copilot prompt

Write a measure that returns Order Amount for the employee Gabi and show that measure in a query broken down by product

…returns the following measure definition and DAX query:

DEFINE
  MEASURE 'Orders'[Gabi's Order Amount] =
    CALCULATE(
      [Order Amount],
      'Employee'[Employee Name] = "Gabi"
    )

EVALUATE
  SUMMARIZECOLUMNS(
    'Product'[Product Name],
    "Gabi's Order Amount", [Gabi's Order Amount]
  )

As you can see, it’s successfully generated the measure definition I wanted and also written a DAX query that shows the results. I’ll admit it’s not the most complex measure in the world but all the same I know plenty of users find Calculate() hard to understand and would find this very helpful. I will also admit that the quality of the results is heavily dependent on whether you have followed best practices for Power BI semantic model design.

Explain DAX topics and apply them to your code

Looking at the measure Copilot just wrote you might think “should I be using the KeepFilters() function here?”. Entering the prompt:

Explain the DAX KeepFilters function

…gives you a nice explanation of what the KeepFilters() function does:

That’s great, but I could get that from a search engine. What is impressive is that the explanation includes a version of the measure above rewritten to use KeepFilters():

DEFINE
  MEASURE 'Orders'[Gabi's Order Amount] =
    CALCULATE(
      [Order Amount],
      KEEPFILTERS('Employee'[Employee Name] = "Gabi")
    )

You can then prompt:

Rewrite the "Gabi's Order Amount" measure so that it uses the DAX KeepFilters function

…and see the rewritten version side-by-side with the original:

…so you can see what exactly has changed and decide whether to accept the change or not.

Explain DAX code

Taking over the ownership of a semantic model that someone else built and understanding their code is tough. Copilot in DAX Query View can help here too. Let’s say you have the following measure in your model:

Avg Order Amount = 
 //Chris said to use the Divide() function
 //to avoid possible division by zero
 DIVIDE(
    [Order Amount],
    [Order Count]
 )

The prompt:

Explain the avg order amount measure

…gives you an explanation of what the measure, and all of the measures it references, does:

It takes code comments as well as the code itself into account and although at the time of writing it doesn’t look at the description properties of your tables, columns or measures it will soon.

Suggest improvements to your measures

Let’s say you have a measure with the following definition:

Order Amount Apples = 
CALCULATE(
    [Order Amount],
    FILTER('Product', 'Product'[Product Name]="Apples")
)

An experienced Power BI developer will read this and immediately hear an (Italian accented) voice in their head saying “Filter columns not tables!” even if in this case the query optimiser will ensure this measure performs well. Most Power BI developers will not see a problem though, and while I’m fairly sure that Tabular Editor’s best practices analyser would also pick up the problem not everyone knows about or is allowed to use Tabular Editor.

In DAX Query View you can enter the prompt:

Optimise the Order Amount Apples measure and explain the suggested changes

…and get a pretty good summary of how the measure could be improved to avoid the use of the Filter() function:

Conclusion

Copilot in DAX Query View is not magic. It’s not perfect and it will hallucinate or make bad suggestions occasionally, although I didn’t cherry-pick any of its output while writing this post and it is getting better and better at an impressive rate. It will not replace all the other tools and techniques you use today to write DAX and it will not put Marco and Alberto out of a job. But there’s a saying inside Microsoft that “Copilot is not a pilot”, which sums up nicely how to think about it: Copilot won’t do your job for you but will help you do your job faster and better. As these examples demonstrate, Copilot in DAX Query View can provide a significant productivity boost, especially for less experienced Power BI developers. If you’re the Power BI admin for a large organisation with a lot of self-service usage then I think that productivity boost more than justifies the cost of the Fabric capacity required to run it.

[And yes, before you comment, I know that the fact Power BI Copilot is only available for F64/P1 and above capacities and has geographic restrictions isn’t ideal, but trust me we’re working on ways to make it more affordable and more widely available. We want as many people as possible to use it but we can’t give it away for free. If you’re comparing this with a free service then remember the old saying: if a product is free then you are the product. You may be ok with that but your employer’s lawyers may not be.]

Exploring Advanced Relationship Properties In Power BI Copilot

I’ve been playing around with Power BI Copilot a lot recently, and following on from my recent posts on tuning Copilot by editing the Linguistic Schema and setting the Row Label and Key Column properties in this post I want to delve into some of the advanced properties of relationships. I won’t pretend to understand everything that’s going on here – I’m still learning all this – but I thought it would be useful to share what I’ve found out so far.

These properties are described in this YouTube video from the Q&A Modeling Bootcamp series that I referred to in my Linguistic Schema post. They available to set in Power BI Desktop on the Relationships pane of the Q&A Setup dialog by clicking on the “Add to relationship” dropdown box” when creating a new relationship:

For my testing I created a semantic model containing a single table called Journeys. Each row in this table represents a leg of a journey; the journeys are identified by the values in the Journey column, each leg is identified by the values in the Stop column. When a person starts a journey there is a row with Stop = 1 and Stop Type = Start; they may then call in on one or more towns, indicated by Stop Type = Call; and when the journey ends there is a row with Stop Type = End. The Mode column contains the mode of transport, the date represents the date of the leg of the journey, and there is a Booked column which tells you when the journey was booked.

For example, in this data Chris started a car journey in London on January 1st, called in on Oxford, then finished his journey in Birmingham on January 2nd.

There’s also a measure called Journey Count which does a distinct count on the journey column:

Journey Count = DISTINCTCOUNT(Journey[Journey])

Now Copilot does a pretty good job with this table without any tuning. For example, the prompt

Whose journeys started in London?

is correctly interpreted most of the time as “Show the person for journeys where Town is London and the Stop is 1”:

This is a great example of where the LLM adds extra smarts that aren’t present in the old Q&A feature. But some similar questions don’t get answered so well. For example:

Whose journeys ended in Glasgow?

…gives this result:

Not only is the number shown irrelevant the answer is wrong, because Helen also ended a journey in Glasgow. So some tuning is needed.

Here’s where I’m going to show what I did and how it fixed the problem but I want to make it clear that there may be better ways of doing this. I’m sure this time next year I’ll look back at this post and cringe and my ignorance but hey, we all have to start somewhere!

I added several relationships to the model, for example this Verb relationship to indicate that People end Journeys in Towns on Dates where Stop Type is “End”.

In this case the People column indicates the subject, the verb is “end” and the objects is the Journeys table. There is a prepositional phrase “in” to indicate that journeys end in towns. Journeys end on the date given in the Date column. The fact that a journey has ended is indicated by a condition that the Stop Type column contains the value “End”. The prepositional phrase, Time and Condition were added with the “Add to relationship” dropdown.

After this I got the following, correct response to the prompt

Whose journeys ended in Glasgow?

Here’s another example. Consider the prompt:

Who passed through Birmingham?

The original model gives the following, reasonable response:

But let’s say that when we write “passed through” we only mean Stop Type = Call, so journeys like the one Chris made that start or end in Birmingham don’t count.

With the following relationship:

…you not only get the correct result:

…you can ask questions like:

Who passed through Birmingham and on which dates?

As you can see, the “Add to relationship” dropdown box adds even more ways to configure relationships and even more ways to tune Copilot. Knowing your English grammar and parts of speech is going to help a lot here – and this is something that may not come easily to someone working in tech. I like this kind of challenge though. Who knows, maybe the ability to set up a Linguistic Schema will be as important as the ability to write DAX for Power BI developers soon?

Tuning Power BI Copilot With Row Labels And Key Columns

A few weeks ago I wrote a post on how to improve the results you get from Power BI Copilot by editing the Linguistic Schema. As I mentioned, though, there are in fact lots of different ways that you as a Power BI semantic model developer can improve the results you get from Copilot and in this post I’ll show you another one: setting the Row Labels and Key Columns properties on a table.

To illustrate this, I built a semantic model with one table in called Customers. Here are the contents:

There are two important things to notice here:

  1. The Preferred Mode Of Address column contains the full name of the customer. Let’s say there is a business rule that specifies that whenever we display a customer in a report, we need to use the Preferred Mode Of Address column.
  2. Some customers have the same name, and individual customers are identified by their CustomerID (this is not a slowly changing dimension so there is one row per customer).

Now, let’s give Power BI Copilot the following prompt:

show customers by age

This is interpreted as “Showing customers sorted by age”, which is fair enough, and here’s the output:

There are a few things wrong here, but the first one to fix is that it’s showing customers by their first name and not the Preferred Mode Of Address column.

We can fix this by going to the Model View pane in Power BI Desktop, selecting the table itself, and setting the Row Label property on it to the Preferred Mode Of Address column:

The Row Label property of a table has been around since the beginning of Power BI but is very rarely used. For tables that represent entities (such as customers) it indicates which column should be used as the name of the entity.

After making this change, the same prompt (which is interpreted in the same way) now returns the following:

This is better because we’re now showing customers by Preferred Mode Of Address. However there’s another problem: the oldest customer is shown as Fred Blogs with an age of 54. There is no single customer called Fred Blogs with an age of 54 though – there are two different customers called Fred Blogs who are 48 and 6 years old respectively, and Power BI has treated them as the same person and summed their ages.

In order to get Power BI to understand that individual customers are distinguished by the CustomerID column, we need to set the Key Column property on the table to CustomerID:

For tables that represent entities, the Key Column property tells Power BI which column uniquely identifies each entity.

After making this change, the prompt now gives us the result we’re looking for:

As you can see, there are now two separate bars for the two customers called Fred Bloggs – there is one bar for each unique Customer ID.

In conclusion it’s a good idea to set these two properties on most, if not all, dimension tables in your semantic model if you’re planning on using Copilot.

[Thanks to Roseanne Levasseur for telling me about the importance of these properties with regard to Copilot]

Get Better Results From Power BI Copilot With Linguistic Modelling

Everyone is excited about Power BI Copilot, and the newly-announced preview of being able to use Copilot to ask questions about all the data in your semantic model rather than just what is shown in a report is a massive step forward. However amazing the LLMs used behind the scenes are, though, the quality of the results your users get from this new Copilot preview depends on a number of factors that you as a developer control. How you model your data is very important and as the announcement blog mentions, so is the linguistic schema that was originally added for Copilot’s predecessor feature, Q&A. Copilot returns much better results than Q&A ever did but the combination of Copilot and the extra information that Q&A’s linguistic schema provides (information Copilot could not know unless you told it) makes Copilot even more powerful. What’s more, you don’t need to edit a YAML file to use this functionality any more because most of the features of the linguistic schema are now available to edit in Power BI Desktop’s Q&A Setup dialog.

In this blog post I’ll show you a few examples of how adding to a model’s linguistic schema improves the new Power BI Copilot preview’s results when you’re querying your semantic model.

Semantic Model

Let’s say you own a farm where you grow fruit. Customers visit the farm to buy fruit and the fruit is picked for these customers by your employees. You store your sales data in a Power BI semantic model that looks like this:

The Orders table is a fact table with one row for each order line. The Order ID column identifies the order that each line item is linked to, the Amount column contains the sales value and the Units column contains the number of fruit delivered for each line. The Employee dimension gives the name of the employee who picked the fruit; the Customer dimension gives the name of the customer who ordered the fruit; the Product dimension gives the name of the fruit picked. Products are associated with Product Groups via a many-to-many relationship.

Here’s the data in the dimension tables:

To get the best results from Copilot note that:

  • The data is modelled as a classic star schema.
  • The table and column names are in human-readable English with no abbreviations and with spaces between the words. I talked about my opinions on Power BI naming conventions in this blog post.
  • All tables and columns that should not be shown in a report have been hidden.
  • The fact table measure columns have been hidden and three explicit measures – Order Amount, Order Units and Order Count (which is a distinct count on the Order ID column) – have been created.

Synonyms

While Copilot performs well on this model, let’s look at a simple question where it doesn’t return the expected result:

Show the number of orders by employee

The prompt returns a visual, but on closer inspection it’s not the result you want. It shows the count of rows in the Orders table which is the number of line items, not a count of orders:

To get the correct result you need to tell Copilot that the Order Count measure returns the number of orders by defining a Synonym. You can do this in the Q&A setup dialog in Power BI Desktop on the Synonyms tab:

Setting “number of orders” as a synonym for the Order Count measure means that the prompt now returns the following visual with the results you want:

Verbs

The next prompt to look at is:

Who picked lemons?

You know that on our farm it’s the employees who pick the fruit but there’s nothing in the model to tell Copilot that. As a result the prompt above results in Copilot saying that it doesn’t know what “picked” means in this context:

On the relationships tab of the Q&A Setup dialog you can fix this by defining a Verb relationship:

The relationship tells Copilot that “Employee names” pick “Product names” with the Orders table connecting the two columns.

With this relationship in place, Copilot correctly answers that Gabi was the only employee who picked lemons:

Nouns

The customer Chris is also widely referred to by employees as “Mr Webb”, but that name isn’t stored anywhere in the model. As a result the prompt

How much money did we make from Mr Webb?

results in the following, fairly reasonable, response:

However with a noun relationship set up to tell Copilot that “Mr Webb” is a kind of customer name where customer name equals “Chris”:

Then the result is what you would expect:

Dynamic Nouns

Copilot does a good job with the many-to-many relationship between Product and Product Group without any optimisation. For example the prompt:

show all citrus fruit and their order amounts

Returns the correct result:

But let’s say that in this case you want to show the individual products rather than the product group “citrus fruit”. You can do this by setting up a dynamic noun relationship:

The relationship is that”Product group names” define kinds of “product names” with the Product To Product Group table linking the two. With this in place the prompt now returns the desired result:

Conclusion

These examples barely scratch the surface of what’s possible with the linguistic schema and Copilot. Apart from the documentation, I found the videos on the (fairly old) “Natural Language for Power BI” YouTube channel which were created when Q&A was launched useful for understanding the concepts here too. There’s a lot to learn here but with some trial and error, as well as listening to feedback from your end users, you should be able to tune Copilot so it returns high quality results almost all the time.

How Much Does Copilot Cost In Microsoft Fabric?

There’s a lot of excitement about Copilot in Power BI and in Fabric as a whole. The number one question I’m asked about Copilot by customers is “How much does it cost?” and indeed there have been two posts on the Fabric blog here and here attempting to answer this question. The point I want to make in this post, though, is that it’s the wrong question to ask.

Why? Well to start off with Copilot isn’t something you buy separately. Every time you use Copilot in Fabric it uses compute from a capacity, either a P SKU or an F SKU, just the same as if you opened a report or refreshed a semantic model. You buy the capacity and that gives you a pool of compute that you can use however you want, and using Copilot is just one of the things you can use that compute for. No-one ever asked me how much it cost in dollars to open a report in Power BI or refresh a semantic model, so why ask this question about Copilot?

Of course I understand why Copilot feels like a special case: customers know a lot of users will want to play with it and they also know how greedy AI is for resources. Which brings me back to the point of this post: the question you need to ask about Copilot is “How much of my Power BI/Fabric capacity’s compute will be used by Copilot if I turn it on?”. Answering this question in terms of percentages is useful because if you consistently go over 100% usage on a Fabric capacity then you will either need to buy more capacity or experience throttling. And if Copilot does end up using a lot of compute, and you don’t want to buy more capacity or deal with throttling, then maybe you do want to limit its usage to a subset of your users or even turn it off completely?

To a certain extent the question about percentage usage can be answered with the Premium Capacity Metrics app. For example, I opened up a gen2 dataflow on a Premium capacity that returns the following data:

I expanded the Copilot pane and typed the following prompt:

Filter the table so I only have products that were produced in the country France

And here’s what I got back – a filtered table showing the row with the Producer Country France:

So what impact did this have on my capacity? Since I know this was the only time I used Copilot the day I wrote this post it was easy to find the relevant line in the “Background operations for timerange” table on the TimePoint Detail page of the Premium Capacity Metrics app:

There are two important things to note:

  • For the 30-second window I selected the operation above used 0.02% of my capacity
  • Copilot operations are classed as “background operations” so their cost is smoothed out over 24 hours. Therefore the operation above used 0.02% of my capacity for 24 hours from a few minutes after the point I hit enter and the operation ran; in this particular case I ran my test just before 16:20 on Friday March 15th and the operation used 0.02% of my capacity from 16:20 on Friday the 15th until 16:20 on Saturday March 16th.

How can you extrapolate tests like this to understand the likely load on your capacity in the real world? With great difficulty. Almost all the Fabric/Power BI Copilot experiences available today are targeted at people developing rather than just consuming, so that naturally limits the opportunities people have to use Copilot. Different prompts will come with different costs (as the blog posts mentioned above explain), a single user will want to use Copilot more than once in a day and you’ll have more than one user wanting to use Copilot. What’s more, going forward there will be more and more opportunities to use Copilot in different scenarios and as Copilot gets better and better your users will want to use it more. The compute usage of different Fabric Copilots may change in the future too. So your mileage will vary a lot.

Is it possible to make a rough estimate? Let’s say you have 20 developers (which I think is reasonable for a P1/F64 – how many actively developed solutions are you likely to have on any given capacity?) writing 20 prompts per day. If each prompt uses 0.02% of your capacity then 20*20*0.02%=a maximum of 8% of your capacity used by Copilot for the whole day. That’s not inconsiderable and I’m sure someone will leave a comment saying I’m underestimating what usage will be.

Which brings me to my last point: should you even see Copilot as being different from anything else you can do in Fabric that consumes compute? Or as an optional extra or additional cost? After all, dataflows consume compute, you can enable or disable dataflows in your tenant in the same way you can enable or disable Copilot, but very few customers disable dataflows because they see the benefit of using them. Turning off dataflows would reduce the load on your capacity but it would also stop your users from being so productive, and why would you do that? If we at Microsoft deliver on the promise of Copilot (and believe me, we’re working hard on this) then the productivity gains it brings to your developers should offset the cost of any extra capacity you need to buy – if indeed you need to buy any extra capacity.

So, to sum up, if you enable Copilot in Fabric you will see additional load on your capacities and you may need to buy more capacity as a result – but the benefits will be worth it. Predicting that additional load is difficult but it’s no different from predicting how your overal Fabric capacity usage will grow over time, as more and more reports, semantic models, notebooks, warehouses and so on get created and used. Rather than doing lots of complex calculations based on vague assumptions to try to predict that load, my advice is that you should use the Capacity Metrics app to monitor your actual usage and buy that extra capacity when you see you’re going to need it.