Why doesn't the MDX Order function with ASC sort all crossjoined hierarchies (as the SSAS doc suggests)?

Question

Version

I am using Microsoft Analysis Services 16.0.43.229.

Question

I do not understand some behaviour of the Order function when using a Crossjoin with ASC ordering as it does not seem to match the documentation. It seems that it is only sorting the last hierarchy, but the documentation says this should only be the case if DESC is used:

https://learn.microsoft.com/en-us/sql/mdx/order-mdx?view=sql-server-ver16

If ASC or DESC is specified, the Order function first arranges the members according to their position in the hierarchy, and then orders each level.

If the Order function is used with a set where two or more hierarchies are crossjoined, and the DESC flag is used, only the members of the last hierarchy in the set are ordered. This is a change from Analysis Services 2000 where all hierarchies in the set were ordered.

Order with 1 hierarchy ASC

The following MDX shows the order for the first hierarchy:

SELECT {[Measures].[Tax Amount]} ON 0,
Order([Sales Territory].[Sales Territory].[Group].Members,
[Measures].[Tax Amount], ASC) ON 1
FROM [Adventure Works]

Territory	Tax Amount
NA	(null)
Pacific	$852,426.90
Europe	$1,584,046.16
North America	$6,348,268.77

Order with 2 hierarchies crossjoined ASC

If I add a second hierarchy in a Crossjoin, I would expect this first hierarchy to remain in that order (as I am using ASC and not DESC), but instead it sorts the first hierarchy in member order:

SELECT {[Measures].[Tax Amount]} ON 0,
Order([Sales Territory].[Sales Territory].[Group].Members
* [Product].[Product Categories].[Category].Members,
[Measures].[Tax Amount], ASC) ON 1
FROM [Adventure Works]

Territory	Category	Tax Amount
Europe	Accessories	$26,238.18
Europe	Clothing	$32,110.44
Europe	Components	$153,348.64
Europe	Bikes	$1,372,348.90
NA	Accessories	(null)
NA	Bikes	(null)
NA	Clothing	(null)
NA	Components	(null)
North America	Accessories	$62,515.40
North America	Clothing	$128,244.56
North America	Components	$774,285.38
North America	Bikes	$5,383,223.44
Pacific	Clothing	$9,054.06
Pacific	Accessories	$13,011.05
Pacific	Components	$16,292.11
Pacific	Bikes	$814,069.68

Please could someone clarify the documentation. Why is it only sorting by the second hierarchy? I would expect the result to be:

Territory	Category	Tax Amount
NA	Accessories	(null)
NA	Bikes	(null)
NA	Clothing	(null)
NA	Components	(null)
Pacific	Clothing	$9,054.06
Pacific	Accessories	$13,011.05
Pacific	Components	$16,292.11
Pacific	Bikes	$814,069.68
Europe	Accessories	$26,238.18
Europe	Clothing	$32,110.44
Europe	Components	$153,348.64
Europe	Bikes	$1,372,348.90
North America	Accessories	$62,515.40
North America	Clothing	$128,244.56
North America	Components	$774,285.38
North America	Bikes	$5,383,223.44

Answer 1

When sorting, ASC will sort within hierarchies. In this case it will sort Categories within each Territory.

To sort categories across all territories you should use BASC, where B stands for "hierarchy breaking".

See https://learn.microsoft.com/en-us/sql/mdx/order-mdx?view=sql-server-ver16