As a Power BI semantic model developer, one of the first things you will learn about is using the Sort By Column property to make sure the values in a column – for example days of the week or months of the year – are sorted correctly. Recently, though, I came across a situation where it was important to set it when you might not expect to have to in order to get good results from Copilot.
Consider the following simple semantic model:
It contains a single table containing sales values broken down by the financial year that products were launched in:
You might think that is isn’t necessary to set the Sort By Column property on the Launch column because, even though it is a text value, sorting it alphabetically gives you the sort order you would expect. Clicking on this column in the Data pane in Desktop to automatically create a visual gives a table with the financial years in the correct order (ie in ascending order by year):
But selecting the Sales Amount measure changes the visual used and, crucially, changes the sort order:
Note that, now, the bar chart is sorted in descending order by the Sales Amount measure, rather than by year.
This type of automatic behaviour is important because it’s something Copilot will rely on. So, for example, the prompt:
Show Sales Amount by product launch dateReturns the same bar chart as a response:
While a prompt to create a new report page does something similar:
Show Sales Amount by product launch date as a new page
Note how, on this report page, both the line chart and the column chart are ordered by Sales Amount again. This is almost certainly not what your end users want to see.
So how can you get Copilot to generate visuals with Launch on the x axis sorted in year order? The solution is to add a new column (in this case called Launch YYYY and hidden) and use it to set the Sort By Column property for the Launch column:
Having done this the two prompts above return visuals sorted by the Launch column and not by the Sales Amount measure. So:
Show Sales Amount by product launch date…returns
…and the prompt:
Show Sales Amount by product launch date as a new page…returns:
This is a very simple example – in the real world you should have a separate date dimension table and follow all the other best practices for data modelling. The point is, though, that setting the Sort By Column property on a column means that sort order will always be respected in visuals created by Copilot unless you explicitly override it in your prompt.
Not very intuitive. Is it because it’s a text column and/or the “date”/”year” name is involved in the prompt (and it has a special meaning)?
I think so – plus the fact that the column name doesn’t mention anything about dates or years
I wonder what would happen if you duplicated the launch date column to…_2 and then sorted the original column on the…_2 column. Just to understand if it’s the explicit sorting that does the trick or if it’s that it’s being numeric or date as in your last post
Your post is soo helpful and informative, sort by column property on a column i agree with your suggestions. that you soo much
A very useful article and source for learning such high-quality information! I appreciate you sharing this useful information.