MDX Sum of Max on a set without using the set in the select

Question

Ok sorry for the weird question title, I've trouble on putting words on what I am trying to accomplish.

I have an OrderLine Fact table. Each row has a product (Category/SubCategory/Product). I need to have a measure that returns the number of Orders (not OrderLines) which have one or more Orderlines in a ProductCategory. Since the Order Fact Table is not linked to Product, I have to get this data from the OrderLine Fact Table.

My knowledge of MDX is rather limited and from what I could think of, all I have found is to put the "Number of Orders" (1 or -1) measure for each OrderLine and have a degenerated dimension of OrderNumber in the OrderLine table. With this, I thought I could get the SUM of MIN of "Number of Orders" by OrderNumber in the OrderLine Fact Table. And it works :

WITH MEMBER [Measures].[Test] AS 
    SUM([Order Number].[Order Hierarchy].CURRENTMEMBER, MIN([Order Number].[Order Hierarchy].CURRENTMEMBER.CHILDREN, [Measures].[Number of Orders]))
    SELECT
        {[Measures].[Number of Orders],[Measures].[Test]} ON 0,
        {[Order Number].[Order Number].&[110100000030-BLA-01745892],[Order Number].[Order Number].&[110100000031-BLA-0]} ON 1
    FROM SalesCube

For an order with 3 lines, it will return to me 3 for [Measures].[Number of Orders] and 1 for [Measures].[Test].

My problem now is if I want to execute this query by Product Categories :

WITH MEMBER [Measures].[Test] AS 
        SUM([Order Number].[Order Hierarchy].CURRENTMEMBER, MIN([Order Number].[Order Hierarchy].CURRENTMEMBER.CHILDREN, [Measures].[Number of Orders]))
        SELECT
            {[Measures].[Number of Orders],[Measures].[Test]} ON 0,
            {[Products].[Category Hierarchy].MEMBERS} ON 1
        FROM SalesCube

it gives me the MIN of all OrderLines which is (-1) and sum it to -1. What I need is for the query to return the sum of min "Number of Orders" for each "Order Number" that contains the product category without having to use the "Order Number" on the row axis.

Does that make any sense?

FrankPl FrankPl · Accepted Answer · 2013-12-17T08:04:18

As far as I understand, the best way to model this would be to have an "Order ID" in the order line fact table, and just define a distinct count on it as the measure [Measures].[Order Count]. There should be no need for complex MDX then.

If you need to subtract the orders with -1, maybe you could convert the -1 and 1 to an attribute with user readable values like "placed" and "reversed". Then the "Order Count" measure would get invisible, and a calculated measure be shown to the user which would be implemented as follows:

([Measures].[Order Count], [Order].[Order Status].[placed]) -
([Measures].[Order Count], [Order].[Order Status].[reversed])

MDX Sum of Max on a set without using the set in the select

1 Answers