ssas - Number or Results and WITH MEMBER Calculation interacting weird

Question

In our MDX Queries, we use a template like the following WITH MEMBER . SELECT SUBSET . FROM (SELECT . FROM .) a lot.

What this particular query should do (and does!) is list all product variants matching a filter, and give the sales amount for it for a month in time. On the same row, we have the sales total of all variants of this product.

We need only a subset of the result (for paging), and the results need to ordered to allow paging of course. Then there is a non empty, so only product variants that were sold in the time period qualify.

This works just fine. Paging also works: putting 30, 60, 90 for Start in SUBSET is gives the correct results.

However, if I put a 1 as Count (SUBSET(..., x, 1)) the result of the "Price all" goes to (null) for each and any row I select with x. If I return two or more rows it works just fine, and all rows have their correct values.

Two results and the correct value in the rightmost column (in case you are wondering how the total of all variants, 67, can be lower than the sales of one particular variant (280): I picked a bad example - there was a return (so a negative sale) for another variant of this product, for 213. The 67 is correct and desired!) One Row and a (null) result

I do not understand how or why the SUBSET count would interfere with the Tuple I select in the WITH MEMBER. If it does it for 30 rows at a time, why not for one?

*edit I just tried, what would happen if I filter in a way that only one result matches (and SUBSET paging is set to "working values" of 0, 30): The same as in the 2nd screenshot, for one result row the WITH MEMBER evaluates to (null). So it is not that SUBSET is the problem, but more general: When there is only one result row!

Could this be a bug in our SQL Server Version? It is SQL 2012 with SP4 and all latest security patches.

WITH
   MEMBER [DIM Date].[HI Year_Month_Day].[CURRENT_MONTH] AS
       [DIM Date].[HI Year_Month_Day].&[20201215].Parent /* Select Month of December */
   MEMBER [MEASURES].[Price all] AS (
       (
             [DIM Products].[HI Products].Currentmember.Parent
           , [DIM Products].[HI Subproduct].[Total]
       )
       , [MEASURES].[Price]
    ), FORMAT_STRING = '#,0' /* Get the parent product total next to every subproduct */

SELECT {
      ([DIM Datum].[HI Year_Month_Day].[CURRENT_MONTH], [MEASURES].[Price])
    , ([DIM Datum].[HI Year_Month_Day].[CURRENT_MONTH], [MEASURES].[Price All])
} ON COLUMNS
, {
    SUBSET(
        ORDER(
            NONEMPTY(
                  ([DIM Products].[HI Produt Subproduct].[Subproduct])
                , {([DIM Date].[HI Year_Month_Day].[CURRENT_MONTH], [MEASURES].[Price])}
            )
            , ([DIM Date].[HI Year_Month_Day].[CURRENT_MONTH], [MEASURES].[Price])
            , BDESC
        )
        , 0
        , 30
    )
} ON ROWS
FROM (
    SELECT
          {[DIM StoreLocation].[HI Country].[Country].&[US]}
        * {[DIM Productgroup].[HI Productgroup].[Productgroup].&[Bikes]}
    ON COLUMNS
    FROM [Cube]
)

question from:https://stackoverflow.com/questions/65898563/number-or-results-and-with-member-calculation-interacting-weird

Answer 1

Waitting for answers