Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mafaber
Helper II
Helper II

Using related in a measure

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!

11 REPLIES 11
Anonymous
Not applicable

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
)





amitchandak
Super User
Super User

@mafaber , hope the above solution has resolved your issue.

Anonymous
Not applicable

"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:
image.png

Please check my reply above. Message #7.



V-lianl-msft
Community Support
Community Support

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..🙂)

AiolosZhao
Memorable Member
Memorable Member

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





Did I answer your question? Mark my post as a solution!

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:

 

Using related in a measure.PNG

 

TARGET = LOOKUPVALUE(DIM2[CBM],DIM2[SI],RELATED(DIM1[SI]))

 

It's a calculated column.

 

Please try.

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

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.

Using related in a measure 2.PNG

 

MEASURE = CALCULATE(SUMX(DIM1,LOOKUPVALUE(DIM2[CBM],DIM2[SI],DIM1[SI])))

 

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.