MDX Calculated Measure Count

Question

I'm attempting to add a calculated measure to a simple MDX query.

Although I have managed simple calculated measures before I clearly don't understand MDX sufficiently to make this work.

Anyway, what I am trying to do is count the number of distinct orders per each of our truck routes. I have tried counting the Order Dimension Keys but all this does is produce the total number of orders in the system rather than the number of orders per route. Can someone help me out? I feel that once I understand this it will make subsequent MDX queries a lot simpler.

With Member Deliveries as

Count([Dim Order].[Order Key].children) --clearly wrong

Select non empty

[Deliveries] on 0,
[Dim Route].[Route Code].Children on 1

From [Defacto DW]

SouravA SouravA · Accepted Answer · 2015-08-13T09:28:08

Let's say you have a measure group which connects to this "Orders" dim. Lets say you have a measure in this measure group called Measures.Order Count (I am assuming you should be having one).

If for any order, any route is taken, then for that order-route combination, that order won't be NULL.

You can then leverage the NonEmpty function to get a count of those non-null intersections.

With member Deliveries as
NonEmpty
        (
         [Dim Order].[Order Key].[All].children, 
         [Measures].[Order Count]
        ).count

Select 
Deliveries on 0,
[Dim Route].[Route Code].members on 1
From [Defacto DW]

Replace the measure with the actual measure in the cube.

MDX Calculated Measure Count

1 Answers