Using related in a measure

Anonymous I have to rephrase my question then, because I wanted to simplify the issue, sorry.

The exact setup is the following:

Tables:

dim_table1 contains Sales Item(SI) and Version Item(VI) numbers.

Sales Item is simply a bigger unit, one SI contains many VIs, but one VI belongs to only a single SI. The dim_table1 is a conversion between the two.

dim_table2 contains CBM values based on SIs.

fact_table1 contains all the data on VI level.

Relationship:

dim_table1 is related to the fact_table1 via the VI columns, and also dim_table1 to dim_table2 via the SI columns.

Measure:

So what the measure needs to do is: match the VI to an SI in dim_table1 then match that SI to a CBM value in dim_table2 and return that value.

I thought using the related function, I could get the related SI to the VI and use that to calculate the CBM value, but since related doesn't work in a measure, it will not work like that.

Hi V-lianl-msft ,

https://drive.google.com/open?id=1WINe4UDKX6_5rtKMtNM-mtj0GA1SD5JC

I can't share the original file, but I created pbix file with some sample data with the same logic.

(Sorry for the google drive, my company OneDrive doesn't allow outside sharing..🙂)

Please check my reply above. Message #7.

"I always try to avoid using calculated columns unless the desired outcome is a categorial value or I want to use it for filtering." 
I don't know what is your data size, but i do use calculated columns whenver possible, as while they slow down the load of the model, they make it much faster in the use. There are some models that I make that CANNOT work without pre-calculated columns, measures make extremely slow.

But anyway, measures and CC serves different uses.  *In this case* it looks like a CC is the best method.
And RELATED works only in row context:

https://docs.microsoft.com/en-us/dax/related-function-dax
(see the box in purple)

So to achieve your result in a measure (even if I said a CC would be better) you have to work in a different way. 

When you are in a FACT you can't use the FILTER CONTEXT (as your filter goes from dim to fact).

So first you have to retrieve the CURRENT value of the row where you are in your table with a SELECTEDVALUE.

Once you have that value, you have to use a LOOKUPVALUE to retrieve the corresponding value in the dim_table1 and then use a second lookupvalue. So, this

Measure = 
var vi= SELECTEDVALUE(fact_table1[Version Item])
var si=LOOKUPVALUE(dim_table1[Sales Item];dim_table1[Version Item];vi)

RETURN 
LOOKUPVALUE(dim_table2[CBM];dim_table2[Sales Item];si)

 
So, it works, but it's a wrong way to use PowerBI. Or, rather than wrong, inefficient.

Here's your result: