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

[PriscillaCamp]

Recently, I have been working on a new report. 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 undertsand my blog. 

Problem

This formula find 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, as 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 forumula 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 data.
• 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.