Forum Discussion
Helper IIUsing related in a measure
Helper IIAnonymous 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 mafaber ,
Sorry for reply late. I created an example based on your instruction. Hope below "Target" column is what you want:
TARGET = LOOKUPVALUE(DIM2[CBM],DIM2[SI],RELATED(DIM1[SI]))
It's a calculated column.
Please try.
Aiolos Zhao
- mafaber
Anonymous Yes, it is, but I was also able to do it using calculated columns. Question is, is it doable using a measure?
I always try to avoid using calculated columns unless the desired outcome is a categorial value or I want to use it for filtering.
So how I imagine the measure would work is something like this:
Match the VI in the fact table to a specific SI in dim_table1, then match that SI to a CBM value in dim_table2 for every VIs in selection and sum up those values.
Thank you for spending time with this!
Hi mafaber ,
Sorry for the late reply, please try below measure, hope that's what you want.
MEASURE = CALCULATE(SUMX(DIM1,LOOKUPVALUE(DIM2[CBM],DIM2[SI],DIM1[SI])))Aiolos Zhao