Home Dashboard Directory Help
Search

DAX result sorting issue by Gerhard Brueckl


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 775557
Opened: 1/2/2013 12:59:59 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

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
Details
Sign in to post a comment.
Posted by Microsoft 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>
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
MDXQuery1.txt 1/2/2013 324 bytes
DAXQuery1.txt 1/2/2013 241 bytes