DAX RELATED not working for me

[DJBrennan]

Hi,
i have a fact table which contains many repetitions of a Supply Number with different usage values by date. I have a dim table that contains same supply number and various prices. I want to create a measure that multiplies usage by RElated price.
I can set out a table that lists supply number from fact table and correctly lists the various price elements from theDim table.

however when I try to write a measure
cost = sum(fact[usage])* RELATED(dim[price] it doesn't work. I am advised that the related table can't be found.

any advice gratefully received
Denis

[OwenAuger · Accepted solution]

@DJBrennan

I would iterate over the values of the dim[price] column or the dim table using one of these two measures:

cost =
SUMX ( VALUES ( dim[price] ), dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )

 or

cost =
SUMX ( dim, dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )

 The red part can be replaced with a measure if you have already defined it.

[OwenAuger · Accepted solution]

@DJBrennan

I would iterate over the values of the dim[price] column or the dim table using one of these two measures:

cost =
SUMX ( VALUES ( dim[price] ), dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )

 or

cost =
SUMX ( dim, dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )

 The red part can be replaced with a measure if you have already defined it.

[Phil_Seamark]

Have you set up a relationship between the two tables?

[DJBrennan]

Yes, of course. that's why the table function perfectly. They're related on Supply Number

de ks

[v-sihou-msft · Accepted solution]

@DJBrennan

In this scenario, since the multiply need to be done on each row level, you need to multiple the RELATED price in SUMX() function.

I assume you have table like below:

You just need to use SUMX() function.

Total Cost = SUMX('Fact','Fact'[Usage]*RELATED(Supply[Price]))

Regards,