Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
simply put I have 2 tables, one containing number of items shipped, and a dim table containing cubic meters (CBM) of each product. They have an active relationship set up based on a product ID, but when I want to use the CBM values in a measure using the related function, it returns an error saying the tables are not related. With calculated column, the exact same DAX code works just fine, but I would like to avoid using a column due to data size. What is the reason it is not working with a measure?
Thank you!
Hi!
You have to create a row context in your formula if you want to avoid the calculated column.
Example of RELATED in a measure
Measure=
SUMX(
TABLE,
COLUMN1*
SWITCH(
TRUE(),
CONDITION USING RELATED,
1,
-1
)
@mafaber , hope the above solution has resolved your issue.
"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:
Please check my reply above. Message #7.
Hi @mafaber ,
Can you use OneDrive for Business to share sample data or sample pbix?(Please mask any sensitive data before uploading)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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..🙂)
Hi @mafaber ,
I think you can't use a column into a measure directly, please try to use below DAX to instead of the column in your measure and verify the data:
calculate(sum(your_cbm_column_name))
Because the relationship between these 2 tables will affect the result, if the result is not what you want, please try to table relationship if you can.
Thanks.
Aiolos Zhao
Proud to be a Super User!
@AiolosZhao 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
Proud to be a Super User!
@AiolosZhao 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
Proud to be a Super User!