DAX result sorting issue | Microsoft Connect

DAX result sorting issue by Gerhard Brueckl

Closed

as By Design Help for as By Design

Type:	Bug
ID:	775557
Opened:	1/2/2013 12:59:59 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

English

private build - 11.0.9000.5

Analysis Services

Windows 7 Enterprise (SP1)

German

run a similar query in MDX and in DAX and check the results

MDX-Query:
SELECT
{
[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]
} ON 0,
[Date].[Calendar Year].[Calendar Year].members ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

DAX-Query:
EVALUATE
SUMMARIZE(
    'Date',
    'Date'[Calendar Year.Key0],
    'Date'[Calendar Year],
    "Internet SA", 'Internet Sales'[Internet Sales Amount],
    "Reseller SA", 'Reseller Sales'[Reseller Sales Amount])

Results MDX-Query:
    Internet Sales Amount    Reseller Sales Amount
CY 2005    3266373,6566    8065435,3053
CY 2006    6530343,5264    24144429,654
CY 2007    9791060,2977    32202669,4252
CY 2008    9770899,74    16038062,5978
CY 2010    (null)    (null)

Results DAX-Query:
Date[Calendar Year.Key0]    Date[Calendar Year]    [Internet SA]    [Reseller SA]
2007    CY 2007    9791060,2977    32202669,4252
2008    CY 2008    9770899,74    16038062,5978
2005    CY 2005    3266373,6566    8065435,3053
2006    CY 2006    6530343,5264    24144429,654
2010    CY 2010

DAX-Query:
Date[Calendar Year.Key0]    Date[Calendar Year]    [Internet SA]    [Reseller SA]
2005    CY 2005    3266373,6566    8065435,3053
2006    CY 2006    6530343,5264    24144429,654
2007    CY 2007    9791060,2977    32202669,4252
2008    CY 2008    9770899,74    16038062,5978
2010    CY 2010

X64

File Name	Submitted By	Submitted On	File Size
DAXQuery1.txt	Gerhard Brueckl	1/2/2013	241 bytes
MDXQuery1.txt	Gerhard Brueckl	1/2/2013	324 bytes

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>