Blog

What You Should Know about the Sort By Column setting in Power BI

Recently, I have been working on a new report for my supervisor. One of my requirements was to find a total of Service Requests based on what month(s) the user selects on the slicer. I thought this would be an easy task, but it led to some difficulties, which at the time I did not realize was due to the behavior of the Sort By Column. I put this blog together so you can see its effect on other columns. Therefore, if you ever come across this issue you know what to do 🙂

This is the raw data for the Requests Details table.

 I also included the Count of Requests Per Month so we know what totals to expect while testing our DAX code.

 This is my calendar table, which has a one-to-many relationship with the Request Details table.

 Note: This is a shortened version of the Calendar table I have in Power BI. I added it to help you understand my blog.

Problem

 This formula finds the Total of Service Requests for the month(s) based on what the user selects on a slicer.

 DAX Code

I place this measure in the fields well, and you can see the field Volume has a total of 4 for April and May. As we know, the total should be 9.

I  decide to test the formula one more time, and select all three months.  You can see we get a total of 3 when it should be 12. What is going on?

My approach is to look in DAX Studio and view my query by selecting the All Queries button, which records all queries that are used in your Power BI connection.

In the code generated by Power BI, I noticed that along with my ‘Month and Calendar Year’ column, used in my DAX code, the ‘Month and Calendar Year Integer’ field is also being used to group my data.

Solution

In order to remove this filter, I must add it to my original DAX code underneath the Month and Calendar Year field.

Let us test this new DAX formula to see if it works. You can now see the Total Volume for the selected months of April and May is 9, and for all three months it’s 12. Hooray, it works!

Conclusion

As you can see, the Sort By Column header can have a big impact on your calculations and it is important to know which fields are being affected so you do not receive an undesired result.  

Please let me know if you have any questions or if you have any feedback! 🙂

Notes:

  • The behavior of the Sort By Columns may be changed at some future date.
  • The Sort By Column effect has been documented by a few other Power BI bloggers. At the time I was working on this problem, I was not aware of this issue or of anybody that had experienced it. I feel more attention needs to be given to this side effect of the Sort By Column, so I created this blog.
  • Using just the Countrows function, in this code is not possible. I have the Month and Calendar Year field added to the Period bucket. And I am using the ALLSELECTED function to remove this implicit filter.
This image has an empty alt attribute; its file name is image-16.png
Tags:

Data Diva

Priscilla Camp is a Business Intelligence and Analytics Specialist at the University of Central Florida. She has 10 years of experience in the field of IT working as a Business Intelligence Analyst. Priscilla started her career working for a startup small business in 2010 and several years later moved over to the Higher Education Industry where she currently resides. Priscilla's focus is T-SQL, Data Warehousing, MS Power BI, and B.I. Strategy.

This Post Has 2 Comments

  1. Oxenskiold July 18, 2020

    Nice write-up of your findings, Data Diva.

    Ouch, yes those sort columns regularly come back to haunt you. I have been there more times than I would like to remember.

    As a quick remedy I usually embed my original measure in an outer CALCUALTE, like so:

    CALCULATE([Originalmeasure], ALL()).

    You just have to make sure that the sort column or sort columns really are sort columns *only* and are not used semantically for other purposes too.

    It would be a nice gesture on part of Microsoft if they would introduce a feature that let us mark a column as a sort-only column and then subsequently ignore those columns in the filter context when a measure is evaluated.

    As you state yourself understanding SUMMARIZECOLUMNS and get to know DAX studio is paramount to getting on top of DAX.

    I have recently posted a railroad diagram showing the syntax of SUMMARIZECOLUMNS in the comments of this article: https://www.sqlbi.com/articles/a-proposal-for-visual-calculations-in-dax/ Perhaps this can help you further on.

    Some of the syntax isn’t used by Microsoft yet, though, however, I think the diagram gives a nice bird’s eye view of this essential to understand function.

    Best regards Jes.

    1. Data Diva October 10, 2020

      Thank you for your comment. I saw your diagram and it is great. I appreciate you sharing it with me.

Comments are closed.