Forum Discussion
Simple DAX issue
Hi,
I have 2 tables:
1. DIM table: CostCenters
-Id
2. FACT Table: Revenue
-OriginalId
-NewId
-Amount
There is a relation between CostCenters.Id and Revenue.NewId and I can easyily calculate the "Sum of Amount" of CostCenter.NewId.
However, I need a measure that gives me the "Sum of Amount" for the CostCenter.OriginalId.
I tried many things but nothing works 😞
Any help would really be appreciated.
Thx Christian
Hi _chris_ ,
If I understand your question correctly then you need follow below steps:-
1. Create a inactive relationship between CostCenters.Id and Revenue.OriginalId
2. Create a measur as below:-
measure_ = CALCULATE ( SUM ( Revenue[Amount] ), USERELATIONSHIP ( CostCenters[Id], Revenue[OriginalId] ) )
3 Replies
Hi _chris_ ,
If I understand your question correctly then you need follow below steps:-
1. Create a inactive relationship between CostCenters.Id and Revenue.OriginalId
2. Create a measur as below:-
measure_ = CALCULATE ( SUM ( Revenue[Amount] ), USERELATIONSHIP ( CostCenters[Id], Revenue[OriginalId] ) )Thank you very much, Samarth!
I had in mind that it is possible to solve problems like this with an inactive relationsship. However, I did not know how to do this exactly.
On the other hand (as I am coming from SQL) where you can just join tables together without the need to add something to the (not existing) model, I am wondering if there is a way to achieve this w/o the need to create a relationship beforehand?
Anyway, I will accept your solution!
BR Christian
_chris_, this is preferred way to achieve it. Rest of the thing depend upon your model and data.
