Simple DAX issue

[_chris_]

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

[_chris_]

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

[Samarth_18]

@_chris_, this is preferred way to achieve it. Rest of the thing depend upon your model and data.

[Samarth_18 · Accepted solution]

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] )
)