MDX for calculating measure given equality of 2 member attributes from 2 different dimensions

Question

I have the following structure of measures and dimensions, and some example data

Fact Table:

id_dim1 id_dim2 id_dim ...measure1

1 2 ...120

2 1 ...101

1 1 ...95

3 3 ...12

Dim1:

id_dim1 member1value

1 Value1

2 Value2

3 Value3

Dim2:

id_dim2 member2value

1 Value1

2 Value2

3 Value3

Dim1 and Dim2 are actually roleplaying dimension and are based on the same dimension table

I want to calculate a measure which sums measure1 only when:

[Dim1].[Hierarchy1].[Level based on member2value]=[Dim2].[Hierarchy1].[Level based on member2value]

In the above example, the measure would be calculated as: 95+12=107

The catch is that I want the measure calculated correctly even though Dim1 and Dim2 are not used in the later mdx query.

So far I have the following, which only works when Dim1 and Dim2 are used in the later query:

Member [Measures].[CondMeasure] AS 'IIF(
[Dim1].[Hierarcy1].[All].[Level].CurrentMember.Name =
[Dim2].[Hierarchy1].[All].[Level].CurrentMember.Name
,
([Measures].[Measure1],
[DimXX].[Hxx].[LevelXX].[MemberXX],
[DimYY].[Hyy].[LevelYY].[MemberYY])
),
0
)
'

The measure is also only calculated in context of some other members, as demonstrated above.

Btw. I am trying this in SSAS

Regards Soeren

Max Max · Accepted Answer · 2011-08-17T20:47:34

What do you think about creation additional field in fact table or expression in DSV?

case 
when id_dim1 = id_dim2 then measure1
else 0
end

after that you can create new metric...

In some situations it's easier than writing difficult mdx-expressions.

MDX for calculating measure given equality of 2 member attributes from 2 different dimensions

1 Answers