MDX : calculated measure filtered by comparison between attributes on different dimensions

Question

I have an OLAP cube via SSAS and I would like to create a calculated measure which will be calculated upon several conditions.
I have a Time dimension and a Dimension1 containing a MinMonthId attribute.
The new measure has to be calculated only after the MinMonthId so I create the measure like this :

NewMeasure = IIF([TimeDimension].[MonthId].currentmember.MemberValue < [Dimension1].[MinMonthId].currentmember.MemberValue, NULL, [Measures].[MeasureA] - [Measures].[MeasureB]);  

It works if MinMonthId appears in the reporting but it doesn't work otherwise, it's empty.

Any Idea ?

FactTable :
Dim1_ID (linked to Dimension1.Dim1_ID)
Month_ID (linked to TimeDimension.MOnthId)
MeasureA
MeasureB

TimeDimension (with hierarchy MonthId -> YearId)
MonthId
YearId

Dimension1:
Dim1_ID
MinMonthId

Answer 1

Hi @Landraille ,

Thank you for your post.

Based on the definition of the NewMeasure , if you did not add MinMonthId , then SSAS will take it's default member.

For default member, please refer to below : If no default member is specified for an attribute hierarchy, and the attribute hierarchy is aggregatable (the IsAggregatable property on the attribute is set to True), the (All) member is the default member. If no default member is specified and the attribute hierarchy is not aggregatable (the IsAggregatable property on the attribute is set to False), a default member is selected from the attribute hierarchy's top level. For more information, please refer to Attribute Properties - Define a Default Member.

So in your scenario, to get the correct value, you would have to add MinMonthId to get it working, or set the default member for MinMonth to 281.

Best regards,
Carrin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you for your answer.
If I set a constant value in MinMonthId, I lose the dynamic process, my Dimension1 is useless.
It's really not possible to get the correct value without displaying MinMonthId ?

Answer 3

If, as it appears from the example, the sole purpose of Dimension1 is to store the underlying data for this calculation, the calculation should be pushed back to DWH. Instead of Dim1_ID, include into the fact table a simple flag showing whether, for this row, MonthID >= MinMonthID. It'll still have to be linked to a dimension, but with just two values, and your measure will simply use the one that satisfies the condition.

Answer 4

Thank you for your answer.
I have simplified the universe relationship in order to explain the problem.
My measures are in different tables :

FactTableA :
Month_ID (linked to TimeDimension.MOnthId)
MeasureA

FactTableB :
Dim1_ID (linked to Dimension1.Dim1_ID)
Month_ID (linked to TimeDimension.MOnthId)
MeasureB

Only the FactTableB has a relation with Dimension1. So I can only put the MinMonthID in this table.
I don't think your solution can work in this case or I'm wrong ?