cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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!

10 REPLIES 10
Highlighted
Super User I
Super User I

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!




Highlighted

@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.

 

Highlighted
Microsoft
Microsoft

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.

Highlighted

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

Highlighted

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!




Highlighted

@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!

Highlighted
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Please check my reply above. Message #7.



Highlighted

"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





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

Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors