Why does a sorting column affect DAX measures?

Hi afaherty​,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

afaherty​ can you provide a PBIX?  Difficult to offer any insight without seeing some data and understanding the DAX in the measures...

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 Sort

Power 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.

Hi afaherty​,
Yes, Sort by column can affect the filter context used by a Power BI visual. Power BI internally needs both Description and Order Sort in the query so it can display the descriptions in the required order. So if your percentage measure removes only the filter from Description.
Please find the attached pbix file with a solution for your scenario. Please let me know if you hae any further questions. Thanks.
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

Please check below points:

1. Check the relationship. Make sure Description in your main table has a valid relationship to the Sort Order table and that the relationship cardinality and filter direction are correct.
2. Check the measure's filter context. If your measure uses ALL, ALLEXCEPT, REMOVEFILTERS, or DISTINCTCOUNT, sorting may reveal an existing filter-context issue.
3. Check for duplicate descriptions. Each description must map to exactly one sort value. For example, Average should always map to 2.
4. Test with a separate dimension table. Create a dimension containing one row per description and its sort order. Sort the dimension's Description column by its Order Sort column, then use the dimension's description in your visuals.

Hi afaherty​,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you