Why MAX is NULL when projecting along the same dimension

Question

Here is a vanilla "Sales" database:

CREATE TABLE Sales(id INT, category VARCHAR(50), item VARCHAR(50), date DATE, amount DECIMAL(10, 2));
INSERT INTO Sales VALUES
    (1, 'Memory', 'Corsair 16GB', '2021-01-01', 200),
    (2, 'Storage', 'Seagate BarraCuda 2TB', '2021-01-02', 50),
    (3, 'Storage', 'Samsung 980 1TB', '2021-01-02', 150),
    (4, 'OS', 'Windows 11', '2021-01-02', 150),
    (5, 'OS', 'Ubuntu', '2021-01-03', 15),
    (6, 'DBMS', 'MySQL Enterprise Edition 8', '2021-01-03', 5000),
    (7, 'DBMS', 'SQL Server 2022', '2021-01-04', 15000),
    (8, 'Memory', 'Corsair 16GB', '2021-01-04', 200),
    (9, 'Memory', 'G.Skill Trident 32GB', '2021-01-04', 250),
    (10, 'OS', 'Ubuntu', '2021-01-05', 15),
    (11, 'DBMS', 'SQL Server 2022', '2021-01-06', 15000),
    (12, 'DBMS', 'MySQL Enterprise Edition 8', '2021-01-06', 5000);

From it we build an SSAS Multidimensional cube with 3 dimensions:

Category with attribute Name,
Item with attribute Name,
Date with attribute Date.

So far so good.

Now creating some measures to get the maximum amounts along some dimensions:

WITH
    MEMBER [Max Daily Sale] AS MAX([Date].[Date].Children, [Amount])
    MEMBER [Max Category Sale] AS MAX([Category].[Name].Children, [Amount])
    MEMBER [Max Category Daily Sale] AS MAX(([Category].[Name].Children, [Date].[Date].Children), [Amount])
    MEMBER [Max Item Daily Sale] AS MAX(([Item].[Name].Children, [Date].[Date].Children), [Amount])
    MEMBER [Max Item Sale] AS MAX([Item].[Name].Children, [Amount])

They are working fine except when one of the dimensions used in the MDX query is also used in the measure.

As an example:

SELECT
    [Category].[Name].Children ON ROWS,
    --[Item].[Name].Children ON ROWS,
    --[Date].[Date].Children ON ROWS,
    { Amount,  [Max Daily Sale], [Max Category Sale], [Max Category Daily Sale], [Max Item Sale], [Max Item Daily Sale] } ON COLUMNS
FROM Sales

Gives:

        Amount  Max Daily Sale  Max Category Sale   Max Category Daily Sale Max Item Sale   Max Item Daily Sale
DBMS    40000   20000           (null)              (null)                  30000           15000
Memory  650     450             (null)              (null)                  400             250
OS      180     150             (null)              (null)                  150             150
Storage 200     200             (null)              (null)                  150             150

All the measures using dimension Category, [Max Category Sale] and [Max Category Daily Sale], result in NULL.

In the same way, using [Item].[Name] as the ROWS axis will "break" the [Max Item Sale] and [Max Item Daily Sale] measures.

And so on for [Max Daily Sale], [Max Category Daily Sale], and [Max Item Daily Sale], when using [Date].[Date].

It's obviously some MDX triviality I'm missing, but which one?

score 0 · Answer 1 · answered Mar 01 '22 at 14:27

You are correct in what you observe. Each dimension can only be used in one section of the MDX: either on ROWS, on COLUMNS, in the WHERE clause, or within a calculated member. Sometimes an MDX statement will appear to break that rule, but look closely and you'll see that the dimension is only being used in one consistent way.

There are ways around this, often with the use of [Dimension name].currentMember but I'm not sure what to suggest for your particular case. Can this report be broken down into separate reports, to simply matters?

Why MAX is NULL when projecting along the same dimension

1 Answers1