Creating an MDX measure in Excel to count members of one dimension based on another dimension

Question

I am trying to create an MDX measure in Excel (in OLAP Tools) that will count how many members there are for every other item in another dimension. As I don't know the exact syntax and notation for MDX and OLAP cubes I will try to simply explain what I want to do:

I have a pivot table based on an OLAP Cube. I have a Machine Number field stored in one dimension, that is the "parent" and for every machine number there is a number of articles that were produced (in certain period of time). Those articles are represented by Order Numbers. Those numbers are stored in another dimension. I would like the measure to count how many order numbers there are for every machine number.

So the table looks like this:

+------------------+----------------+
| [Machine Number] | [Order Number] |
+------------------+----------------+
| Machine001       |                |
|                  |      111111111 |
|                  |      222222222 |
|                  |      333333333 |
| Machine002       |                |
|                  |      444444444 |
|                  |      555555555 |
|                  |      666666666 |
|                  |      777777777 |
+------------------+----------------+

and I would like the result to be:

+------------------+----------------+------------+
| [Machine Number] | [Order Number] | [Measure1] |
+------------------+----------------+------------+
| Machine001       |                |          3 |
|                  |      111111111 |            |
|                  |      222222222 |            |
|                  |      333333333 |            |
| Machine002       |                |          4 |
|                  |      444444444 |            |
|                  |      555555555 |            |
|                  |      666666666 |            |
|                  |      777777777 |            |
+------------------+----------------+------------+

I've tried using the COUNT function with EXISTING as well, but it wouldn't work (always showing 1, or the same wrong number for every machine). I believe that I have to somehow connect those two dimensions together so the Order Number is dependent to Machine Number, but lacking the knowledge about MDX and OLAP Cubes I don't even know how to ask Google how to do that. Thanks in advance for any tips and solutions.

Answer 1

Your problem basicly is, you have two attributes in diffrent dimensions. You want to retrive the valid combinations of these attribute, further you want to count the number of attribute values avaliable in the sceond attribute based on the value of the first attribute.

Based on the above problem statement, in an OLAP cube a fact table or a Measure defines the relations between attributes of diffrent dimension linked to the Measure\Fact-Table. Take a look at the example below.(I have used the SSAS sample db Adventureworks)

--Iam trying to find the promotions that were offered for each product category. select [Measures].[Internet Sales Amount] on columns, ([Product].[Category].[Category],[Promotion].[Promotion].[Promotion]) on rows from [Adventure Works]

Result The result is cross-product of all the product categories and the promotions. Now lets make the cube return the valid combinations only.

select 
[Measures].[Internet Sales Amount]
on columns,
nonempty(
([Product].[Category].[Category],[Promotion].[Promotion].[Promotion])
,[Measures].[Internet Sales Amount])
on rows
from 
[Adventure Works]

Result

Now we indicated that it needs to return only valid combinations. Note that we provided a measure that belonged to the fact connecting the two dimensions. Now lets count them

with member 
[Measures].[test]
as
count(
nonempty(([Product].[Category].currentmember,[Promotion].[Promotion].[Promotion]),[Measures].[Internet Sales Amount])
)

select 
[Measures].[Test]
on columns,
[Product].[Category].[Category]
on rows
from 
[Adventure Works]

Result

Alternate query

with member 
[Measures].[test]
as

{nonempty(([Product].[Category].currentmember,[Promotion].[Promotion].[Promotion]),[Measures].[Internet Sales Amount]) }.count

select 
[Measures].[Test]
on columns,
[Product].[Category].[Category]
on rows
from 
[Adventure Works]