Learning MDX now a days. I have a fact table. Schema + data is following

ColumnID1   ColumnID2   Amount
O1          null        100
O2          null        200
O3          null        300
C1          null        200
C2          null        400
O5          C2          300  
O4          C1          400  

Query is to find the ColumnID2 not null values and add the amount of the row for that ColumnID2 Id to amount in matching ColumnId1 row. e.g

Output

ColumnID1   ColumnID2   Amount
O1          null        100
O2          null        200
O3          null        300
C1          null        200
C2          null        400
O5          C2          300  + 400 
O4          C1          400  + 200

How this can be done in the MDX? Cross join is the thing coming to my mind but does not seem the right approach as it creates the cross product of the rows

Hi ANREV107,

According to your description, you want to aggregate the Amount if the ColumnID2 is not null and it equals the ColumnID1. Right?

In Analysis Services, we build the relationship between fact table and dimension table. Those fact data can only be aggregated based on same values in the foreign key column pointing to dimension table.

In this scenario, I'm not sure the relationship between ColumnID1 and ColumnID2. If it's parent-child relationship, you should modify the data like below so that the amount can be aggregated based on ColumnID1.

ColumnID1   ColumnID2   Amount
O1                null            100
O2                null            200
O3                null            300
C1                null            200
C2                null            400
C2                O5             300  + 400
C1                O4             400  + 200

If this is an expanding and merging relationship, to the ColumnID1 "C1", it's just slowly changed into "05", so it's better to implement a slowly changing dimension. Please see: Implementing Slowly Changing Dimensions.

If that's still not the right approach for your scenario, you can only modify the fact table in DSV with Named Query(T-SQL).

 

Regards,

Could this a Parent/Child Dimension where MembersWithData=LeafDataVisible?