Forum Discussion
Helper VWhy does a sorting column affect DAX measures?
I have had this issue happen to me many times over the past few years and I don't understand it, so I was hoping someone else might!
Let's say I have a table of student grades:
| StudentID | Test | Description | Order Sort - From Sort Order Table |
| 1 | Test 1 | Average | 2 |
| 1 | Test 2 | Average | 2 |
| 1 | Test 2 | Average | 2 |
| 2 | Test 1 | Above Average | 1 |
| 2 | Test 2 | Average | 2 |
| 2 | Test 3 | Below Average | 3 |
| 3 | Test 1 | Average | 2 |
| 3 | Test 2 | Below Average | 3 |
| 3 | Test 3 | Below Average | 3 |
| 4 | Test 1 | Average | 2 |
| 4 | Test 2 | Average | 2 |
| 4 | Test 3 | Average | 2 |
| 5 | Test 1 | Above Average | 1 |
| 5 | Test 2 | Above Average | 1 |
| 5 | Test 3 | Above Average | 1 |
There is a relationship between the table above and this "Sort Order" table below:
| Description | Order Sort |
| Above Average | 1 |
| Average | 2 |
| Below Average | 3 |
Next, I have some measures that calculate percentages that do not involve the "Order Sort" column at all. I have charts and matrices that utilize these measures. Everything looks great. However, if I then go into the data view of the first table, select the "Description" column, and sort by the "Order Sort" column, all of my percentages in my charts/matrices change, and they are all incorrect.
I am going crazy and have given up on trying to sort since it ruins my measures!
3 Replies
- ShivekMaharaj
Community Champion
Hi afaherty,
Yes, this can happen, and the reason is that Sort by Column is not purely cosmetic.
When you set:
Description -> Sort by -> Order SortPower BI can include both Description and Order Sort in the internal query used by the visual.
So even if your measure does not explicitly reference Order Sort, that column can still become part of the filter/grouping context in which the measure is evaluated.
This becomes especially noticeable with percentage measures that do something like:
ALL ( Table[Description] )or
REMOVEFILTERS ( Table[Description] )because that only removes the filter from Description.
The filter on Order Sort can still remain.
A common fix is to clear both columns together, for example:
CALCULATE ( [Your Measure], REMOVEFILTERS ( 'Grades'[Description], 'Grades'[Order Sort] ) )or, if appropriate for the calculation:
CALCULATE ( [Your Measure], ALL ( 'Grades'[Description], 'Grades'[Order Sort] ) )This is a known DAX pattern with Sort by Column. SQLBI has a good explanation of the issue in this article on Sort by Column and filter removal.
Microsoft’s Sort by Column documentation also confirms that the display column and sort column are tied together and must exist at the same granularity.
So I would not remove the sorting itself.
Instead, review any measure where you intentionally remove the Description filter and make sure you also remove the corresponding Order Sort filter when that is what the calculation logically requires.
That is why the numbers appear to change even though Order Sort is never referenced directly in the measure.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
- Jihwan_Kim
Super User
Hi,
when the description is not sorted by the sort order column, and when run the visualization's dax query view,
the DAX query does not contain sort order column, so the measure does not need to include sort order column.
However, when the description column is sorted by the sort order column, then you query it,then, the query contains sort order column, even the visual does not show sort order column, so in this case, measure need to contain this sort order column as well.
- littlemojopuppy
afaherty can you provide a PBIX? Difficult to offer any insight without seeing some data and understanding the DAX in the measures...
