topic Simple DAX issue in DAX Commands and Tips

Simple DAX issue

_chris_ — Tue, 08 Nov 2022 08:17:09 GMT

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

Re: Simple DAX issue

Samarth_18 — Tue, 08 Nov 2022 09:23:41 GMT

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

Re: Simple DAX issue

_chris_ — Tue, 08 Nov 2022 09:26:21 GMT

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

Re: Simple DAX issue

Samarth_18 — Tue, 08 Nov 2022 10:02:29 GMT

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