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