DAX result sorting issue - by Gerhard Brueckl

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 775557 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/2/2013 12:59:59 AM
Access Restriction Public

Description

when querying a multidimensional model using DAX the results are not sorted the same way as they would be if I run a similar MDX query

it further seems that the DAX result is sorted by the internal DATAID's that SSAS generates when building a multidimensional model

as we have the option to specify a sorting column in our model, DAX should also be aware of this and sort the results automatically without having to specify an ORDER BY clause manually
It should behave similar to specifying a "Sort by Column" in a tabular model
Sign in to post a comment.
Posted by Supriya [MSFT] on 1/3/2013 at 11:20 AM
The DAX query needs an Order By clause like this in order to give the sorted results.
EVALUATE
SUMMARIZE(
'Date',
'Date'[Calendar Year.Key0],
'Date'[Calendar Year],
"Internet SA", 'Internet Sales'[Internet Sales Amount],
"Reseller SA", 'Reseller Sales'[Reseller Sales Amount])
Order by 'Date'[Calendar Year.Key0]

CSDL for Adventure Works has the relevant hint in the bi:OrderBy element for the "Calendar Year" column which the user should use to correctly sort the results.

<Property Name="Calendar_Year" Type="String" MaxLength="Max" Unicode="true" FixedLength="false" Nullable="false">
<bi:Property Caption="Calendar Year" ReferenceName="Calendar Year" Contents="Years">
<bi:GroupBy>
<bi:PropertyRef Name="Calendar_Year_Key0" />
</bi:GroupBy>
<bi:OrderBy>
<bi:PropertyRef Name="Calendar_Year_Key0" />
</bi:OrderBy>
</bi:Property>
</Property>