Press "Enter" to skip to content

Considerations When Using Sort By Column In DAX

Marco Russo shows us some things to keep in mind when using Sort By Column in DAX:

A query in MDX automatically inherits the correct column sort order from the data model; the result of an MDX query is always sorted according to the Sort By Column setting. However, DAX does not have any implicit sort order for the columns other than the natural sort order of the underlying data type. For this reason, a DAX query must always specify the sort order in an ORDER BY condition – similarly to a query in SQL. Because DAX requires for a column used in ORDER BY to be part of the query result, a Power BI visual that sorts a column always generates a query that includes at least two columns: the column requested in the report and the underlying column used in the Sort By Column setting. In other words, a Power BI visual showing data by Month must generate a query that contains both Month Name and Month Number, whereas MDX only requires the Month Name column.

There’s also an interesting example where Power BI behaves differently from Excel.