Hello Power BI Fans! It’s good to be back with another article on Copilot within Power BI. As a quick recap, this is a part of the Copilot series, where I want to deep dive into capabilities, and assess the impact on Capacity consumption. Articles within the series are:
- Power BI Copilot – Introduction
- Copilot in Power BI Desktop
- DAX Query View – current article
- Copilot Pane
- Copilot in Power BI Service
Today we will cover the Copilot within Power BI Desktop, specifically, the one available as a part of DAX Query View. First available Copilot in Power BI Desktop was available within Quick Measure, I will not focus on it today for two reasons. First – I was not very satisfied with tests when it was released. Second – since Copilot is available in DAX Query Vier, the Quick Measures version becomes a bit obsolete. I find the DAX authoring experience much better in DAX Query View. Another Copilot in Power BI Desktop is responsible for Generating Measure Descriptions. If I could ask one question to Microsoft about Copilot, I would ask “who asked for this feature?”. Maybe I am too simple man to wrap my head around it, but to me it looks like this is one of the features that no one asked for and using it is a pure waste of Capacity resources. So, the scope for today is clear. We are talking only about DAX Query View.
Don’t be afraid of the scroll size in this article, there is a lot of screenshots 🙂
Setting up the stage
Before we begin, let’s introduce two definitions:
- prompt – our input to Copilot (a question)
- output – Copilot’s answer based on our input
Also, it is important to mention that for my test I am using P2 Premium Capacity, equivalent of F128 Fabric Capacity. Whatever results we have in the end, we must multiply it by 2 to show the numbers for P1 Capacity (F64).
I have nothing to hide, and I will describe everything in detail, so the Capacity Consumption results are reliable. The dataset I am using for test is Retail Data Model, available as a sample dataset within Microsoft Fabric Pipelines. I am loading it Lakehouse and from there straight to Power BI Semantic Model.
My Model is not complicated, the only that I will use here to test the Copilot is a relationship between dimension_date and fact_sale tables:- dimension_date[Date] -> fact_sale[DeliveryDateKey] – active
- dimension_date[Date] -> fact_sale[InvoiceDateKey] – inactive
There is one more thing needed here. We must enable Copilot in Power BI Desktop:
Go to Options -> Global -> Copilot (preview) -> Select a workspace. Workspace must be backed by paid capacity of size at least P1 (for Premium) or F64 for Fabric. If you can’t use Copilot, it means:
- You don’t have access to paid capacity.
- Capacity size is not sufficient.
- Copilot is not enabled in your organization.
- Power BI Desktop consumption of Copilot is deactivated in Capacity settings.
Once all is set, we are ready to go. Let’s go to DAX Query View and start using Copilot.
Warmup prompts
You just loaded the data into your semantic model and set relationships. Assuming you didn’t do any data profiling yet in Power Query, you just want to understand the model a bit better. You can ask a basic question like the one below:
Let’s debunk first myth from demo videos – it’s not that fast as you can see in the demo. But it actually produced a result and it’s not the worst. I asked specifically to run data profiling, but it didn’t provide any results:
I asked simpler question, to provide column statistics for a single table (fact_sale), again, I didn’t receive anything:
I cheated a bit with this question, as I asked it knowing that column statistics is one of the options available as quick queries in DAX Query View. It is a bit disappointing that Copilot didn’t understand one of own Power BI features. To showcase what do I mean, here is an example:
DAX Query View provides a feature, that is pretty smart on its own. the DAX you see on the screen creates a single row in summary table. Full DAX Code contains this code multiplied by number of columns available in the fact_sale table. But ok, let’s move on.
Maybe you have a DAX Query that you want to explain with help of Copilot. In DAX Query View select all the code you want to explain and send prompt to copilot. Here is the DAX code to explain, prompt, and output:
Description is pretty decent I must say, and it’s even detailed enough to explain DEFINE and EVALUTE keywords, that are not obvious if you use DAX Query View for the first time. This is definitely a good thing.
Last basic interaction with Copilot I want to cover is the help that it can provide to explain DAX formulas. You could for example ask Copilot to explain the most common DAX Functions:
Let’s look closer at (in)famous CALCULATE function:
I like this explanation for two reasons. It clearly mentions the purpose of the function, which is being context modified. Another thing worth noticing is that Copilot used my semantic model data to generate examples. That is extremely useful. Few words before we finish this section. Although I like the quality of help delivered by Copilot to understand DAX functions, I kind of feel that this should be a part of standard documentation. Using Copilot bears a certain cost, and rich description of DAX functions should be available for free in my opinion.
We ‘ve sent some basic prompts to Copilot and I believe that output is acceptable, so let’s move and try to generate something.
Generate DAX with Copilot
Let’s start to analyze the data. We could as Copilot to provide a summary of sales per customer, this is the output generate by Copilot:
Once we are happy with output, we can decide to “Keep it”. Once thing to complain – we don’t see the results here. If I don’t know much about DAX, I am not sure if I am happy with it or not. Regardless, this is a good DAX Query, and I am particularly happy about “sales” calculation. First of all, Copilot understood that I have a “fact_sale” table. Second, it correctly picked up TotalIncludingTax column, out of 4 available columns with names starting with “Total…”. There is nothing wrong with this DAX, but I noticed that Customer column is too granular, as it contains the details down to specific address. I would like to ask Copilot to use different column instead for grouping:
Once we are happy with output, we can decide to “Keep it”. Once thing to complain – we don’t see the results here. If I don’t know much about DAX, I am not sure if I am happy with it or not. Regardless, this is a good DAX Query, and I am particularly happy about “sales” calculation. First of all, Copilot understood that I have a “fact_sale” table. Second, it correctly picked up TotalIncludingTax column, out of 4 available columns with names starting with “Total…”. There is nothing wrong with this DAX, but I noticed that Customer column is too granular, as it contains the details down to specific address. I would like to ask Copilot to use different column instead for grouping:
Now, I don’t even need to see the results, I asked specifically about the column I needed, and Copilot picked it up correctly. I can accept the solution. Here is the results of our analysis:
Let’s say that I would like to add a Month information to this summary:
Of course, I was specific enough to mention that I want a column related to fiscal information (there is also a CalendarMonthLabel available in the dataset), but still, I think we can be happy with the result.
So far so good. Let’s do some heavy lifting now. I will ask Copilot to show the monthly summary per customer, but only for the last month where sales occurred:
This is not something that meets my requirement, as it looks at the maximum value of FiscalMonth available at the model level. It must be calculated specifically per customer. I want to give a Copilot more fighting power, and I created a new column with numeric representation of FiscalMonthYear. I will come back to previous summary per FiscalMonth and change the column to my new numeric column, then I will try again:
This time my prompt was very specific, but we can already tell, that result is not correct. Not only Copilot picked back the wrong columns, but also the entire formula starts with wrong definition of last month. It calculates the maximum value of FiscalMonth from perspective of Customer Dimension table. It means, that it will simply pick the maximum FiscalMonth value from Date table, which will be the same for each customer. This time, my prompt was super specific:
This time Copilot gave us a nice Code Diff and we clearly see the addition of BLANK check in relation to TotalIncludingTax value. This looks promising, let’s keep the result. But result is still an empty table. Why? Answer is pretty obvious when we look at our model. Formula tries to go from Customer to Date table, through fact_sale, the problem is that we don’t have a bidirectional relationship between fact_sale and dimension_date:
Formula wants to take the “red” path, while relationship work in the direction specified by “green” arrows. Let’s try to suggest the solution to Copilot:
Again, prompt was very specific, but addition of ALL function here changes nothing. It doesn’t impact the relationship at all. I will remove what was added in recent output, and ask following question:
I think that this time was extremely specific. I mentioned that there is a single direction relationship between fact_sale and dimension_date tables, and we must enable cross filtering in both directions between them. Again, we have ALL(‘dimension_date’) added to the formula, that changes nothing. I will make a last attempt before giving up:
Finally! Or not 🙂 InvoiceDateKey is a column used in inactive relationship between fact_sale and dimension_date. Why Copilot picked that one? No clue. But let’s change it quickly and see if it works:
It does work! We pushed it to the limits, but finally got the result we needed. We could say that I septically told the Copilot what to calculate, but to be fair, it was pretty complex calculation to start with. Props to Copilot for coming up with neat solution to build the dictionary table of Customer – FiscalMonth values, that will be later used in TREATAS function. This is something I didn’t expect to see, so, I am still positively surprised. The thin I needed to show here is that it’s not that straight forward to use the Copilot, and we will not always get what we want with a single prompt. We will be forced to go back and forth and validate the results. I will show one more example here, using the relationships between dimension_date and fact_sale tables.
Generating Measures with Copilot
Summary we generated with Copilot is great, but most of us writes DAX to generate some measures. Let’s try that. You know my model already; I will try to create two simple measures. First, I will start with simple one – TotalSales. I did one mistake here, as I should’ve open new Query Window, as the results I received now was “polluted” by existing chat history. It is important to mention it here, as we want to assess Capacity Utilization in the end, and I will add that one failed attempt to total picture. I opened new dialog window, and started over:
This time everything works as expected. Copilot again understood which the column is to be used in calculation. I am happy with it so I will keep the result. Of course, to use the measure we must add EVALUATE statement, and call the measure in the context of the table – using curly brackets:
The great thing about DAX Query View is that this is not only a “polygon” area to test the code, but we can update our model to include the new measure. Which I am about to do.
Now, the trick is that I want to leverage the fact, that I have inactive relationship in my model based on InvoiceDateKey, that I want to use to calculate Total Invoiced Amount:
Copilot didn’t understand what my prompt, but let’s try to help:
As you can see, my prompt was very specific. The only thing is that I didn’t mention the exact name of columns that take part in the inactive relationship. Copilot guessed it correctly. Again, we can Keep the results, add a measure to a model, and we are done 🙂
Capacity utilization
We went through a lot in this article. Including the failed prompt, I generated exactly 20 of them. Based on what we did, I guess it’s fair to assume that I didn’t exaggerate here. We needed to go back and forth couple of time, to make the end result meet our requirements. If you are a Power BI Developer and you want to use Copilot to help you write DAX Measures, you can easily generate 15-20 prompts within a single session. We will not talk about whether the generated DAX could be optimized or not, DAX code worked in the end and that’s all that matters now. Accuracy of output generated by Copilot is not perfect yet, but for sure will be better and better. Now, the most important question (at least to me and other Fabric Admins), how much does it cost? Let’s look at the MS Fabric Capacity Utilization App:
The only identifier of the operations I’ve done, is the Workspace nam associated with Copilot settings in Power BI Desktop. Workspace name is obfuscated in my screenshots, but this is the first value available in Item column. Next item is Artifact type, which in this case is SemanticModel. Last part is Artifact name, which is anonymous. Published artifacts have their name available. Here we are working in our local machine, that’s why Artifact name is simply Power BI Session Desktop. Filtering that helps find Copilot usage is Operation type set to Copilot in Fabric. If you have too many Copilot related operations, further to the right in the table there are two columns: Smoothing Start and Smoothing End. Smoothing starts after operation is completed, you may look at it identify the sessions you are looking for.
The total cost of all of these operations is 51 055.2 CU (s), which stands for 0.46% of Base capacity. Is it a lot? Yes and no. We could have over 200 sessions like the one presented in this article within 24 hours period. However, let’s not forget I am using P2 Capacity (F128), translating it to P1 gives us 0.92%. Also, looking at the Copilot usage vs 100% of total Capacity may not be the best thing to do. Most probably we run the Copilot on Capacity which serves other workloads, semantic model refreshes, fabric artifacts, report consumption, etc. That’s why we don’t really have 100% to spare for Copilot. As an admin, you know how much Capacity is left for Copilot. Is it 15%, 20%? Remember, overconsumption for longer than 24 hours leads to complete block of Capacity, all the background and interactive operations are stopped. Twenty Power BI Developers using Copilot like in my example, will add additional 9% of Base Capacity (18% for P1 Capacity!).
Why it’s bigger problem than we could think? As mentioned, Copilot is a background operation, smoothed over 24 hours period. Even though for me it’s purely interactive operation. Interactive operations are smoothed over 5 minutes. What if Copilot was interactive activity? There is 288 intervals of 5 minutes length within 24 hours, therefore 288 * 0.46% = 132.48% (over 260% for P1 Capacity!). This is really a lot. No wonder it was made a background activity, but the result is Capacity Consumption being completely unpredictable.
Conclusion
We did quite a decent testing of Copilot within Power BI Desktop. One thing we can say for sure is that using Copilot is not that easy as it looks during the demos. I was never too hyped about it, that’s why I must say I was happy to see some of the outputs generated. I would be very careful recommending it to Power BI beginners, especially by selling it as a solution that will solve all their problems. You must be (at least in current stage of Copilot) a fairly technical person, to be able to validate the results, and iteratively ask better and better questions, leveraging the knowledge about semantic model.
Cost of the Copilot session is manageable but can be tricky as it is hard to scale at organization level. Luckily, Power BI Desktop Copilot is the one that can be turned off on Capacity level, as an admin, you have some fighting power to fight against capacity throttling.
We still have Power BI Service-related test ahead of us, so it’s too soon for a final word. For now, I am happy that you managed to stay with till the very end of the article. Thank for reading and see you in next article 🙂
