Sort measures where row and column axis are cross-joined in MDX

Question

I've a table where there's two dimensions in the row axis crossjoined and two dimensions plus a measure crossjoined in the column axis. Eg:

I would like to sort the rows of a specific column, taking the image as an example I would like to sort all rows so the ones under France -> apples -> avg_sales are sorted. Eg:

I've been able to find many examples on how to do this when there's just a bunch of measures as columns (with no crossjoin) and many different dimensions crossjoined in the row axis but I'm not able to find anywhere how to solve this particular situation.

I tried something like the following:

SELECT CrossJoin(
        [Country].[Country].members,
        CrossJoin(
            [Product Category].[Product].members,
            {[avg_sales]}
          )
        ) on columns, 
        Order(
            Filter(
                CrossJoin(
                    [Date].[Year].Members,
                    [Date].[Month].Members
                ),
                NOT IsEmpty(avg_sales)
            ),
            CrossJoin(
                [Country].[Country].[France],
                CrossJoin(
                    [Product Category].[Product].[apples],
                    {[avg_sales]}
                )
            ),
            BDESC
            ) ON ROWS 
FROM [Main Cube]

But MDX Order() function signature doesn't allow it, it complains by saying:

Mondrian Error: No function matches signature 'Order(<Set>, <Set>, <Symbol>)'

score 1 · Accepted Answer · answered Apr 08 '22 at 19:45

This MDX expression on the row axis would do the trick:

ORDER(
   CrossJoin([Date].[Year].Members, [Date].[Month].Members), 
   ([Measures].[avg_sales], [Product Category].[Product].[apples]),
   BASC
)

If you wanted to sort the months by sales but still keep the years together, then you could do this:

ORDER(
   CrossJoin([Date].[Year].Members, [Date].[Month].Members), 
   [Date].[Year].CurrentMember.ORDERKEY,
   BASC,
   ([Measures].[avg_sales], [Product Category].[Product].[apples]),
   BASC
)

You could even sort the rows by the average sales across the selected countries and fruits on the column axis.

There's a free trial version of Pentaho EE which includes an OLAP client (Pentaho Analyzer) on Mondrian that generates these types of MDX expressions. You can use their Data Source Wizard to load your data, build a few reports and study the MDX to see how the ORDER MDX function is used.

https://www.hitachivantara.com/en-us/products/data-management-analytics/lumada-dataops/data-integration-analytics/download-pentaho.html

score 0 · Answer 2 · answered Mar 01 '22 at 07:04

By the doc https://mondrian.pentaho.com/documentation/mdx.php there is only 2 functions:

<Set> Order(<Set>, <Value>, <Symbol>)
<Set> Order(<Set>, <Value>)

The Order function must be some like this

 Order(
            Filter(
                CrossJoin(
                    [Date].[Year].Members,
                    [Date].[Month].Members
                ),
                NOT IsEmpty(avg_sales)
            ) *
            CrossJoin(
                [Country].[Country].[France],
                CrossJoin(
                    [Product Category].[Product].[apples],
                    {[avg_sales]}
                )
            ),
            BDESC

)

Sort measures where row and column axis are cross-joined in MDX

2 Answers2