Continuing my occasional series of ways you can improve the results you get from Power BI Copilot by setting properties on your semantic model, in this post I’ll look at how setting the Summarize By property can have an impact.
Let’s say you have a semantic model that contains the following table of temperature readings at different English seaside resort towns over the summer:
Now let’s say you enter the following prompt in the Copilot pane on a blank report (this forces Copilot to query the semantic model rather than look for answers from visuals in the report):
show temperature by resortHere’s the response:
As you can see, Power BI Copilot has summed up the temperatures for each resort and this does not make sense. You could argue that the prompt should be more precise and specify the aggregation method to use, but it’s unlikely that most end users would know to do this on their first attempt at prompting or even ever. You could also argue that it would be better to expose an explicit measure and hide the underlying Temperature (C) column in the model – indeed, this is what I would do – but the downside of this is that it may limit the type of analyses that an end user can do.
Let’s say that what you really wanted to see for this prompt is average temperature by resort, and as a semantic model developer you can ensure this happens by setting the Summarize By property on the Temperature (C) column to Average, either on the Column Tools tab in the ribbon or in the Model View:
After making this change, the same prompt now returns the average temperature for each resort:
As with some of the other properties such as Row Label and Key Column I’ve talked about in this series, the Summarize By property is not new but it’s a lot more important to set it when designing models for Copilot.
One last thing to note is that as well as specifying how a column should be aggregated, the Summarize By property can be used to specify that values in a column should never be aggregated at all. This is particularly useful for numeric columns that contain categorical data such as year or zip codes. You can do this by setting Summarize By to None in the model view:
…or, if you’re setting this property on the Column Tools tab in the ribbon, using the Don’t Summarize option:
Almost all models we build implement calculation groups, which requires disabling implicit aggregations. You mention exposing an explicit measure as a solution, but mention “… it may limit the type of analyses that an end user can do.”
I feel this is the inherent issue with Copilot – In its rush to incorporate AI into every product, Microsoft only implement shallow integrations. As a result, Copilot produces usable insights with only the simplest of models; and even then, it requires a change to standard practices.