How to retreive constant value from related table

[EricPad]

Hello - 

I have a PBI report I am working.  When I select a row from my table visual I want to retreive a quantity on hand from a related table. The table visual contains orders ordered by Item Code. I have an item table that has one row per item code. There is a total quantity on hand value that I want to retrieve from that table. 

When I attempt to select that value, it just returns the count and gives me the option to chose a different aggregate. I just want the single value for a given item code.

My report is displayed below as is my data model. 

The table on the right (ItemOnOrderAndForecast) contains the orders and the tbl_CI_Items contains one row per ItemCode. 

Tbl_CI_Items has a one to many relationship with ItemsOnOrderAndForecast. 

Do I need a measure to accomplish this? If so what should the measure look like?

Thank you in advance for your help!

[lbendlin]

What you are doing is pulling a dimension attribute into the fact table.  That is often not required, but if you feel you need to do it anyway then you have to create a calculated column (not a measure) in your fact table  that says 

fact on hand = RELATED('tbl_CI_ITEMS'[TotalQuantityOnHand])

[Anonymous · Accepted solution]

Hi @EricPad

I've made a test for your reference:

1\I assume there are two tables

2\Create a measure for ItemOnOrderAndForecast

 

TestMeasure = LOOKUPVALUE(tbl_CI_ITEMS[TotalQuantityOnHand], tbl_CI_ITEMS[ItemCode], Max(ItemOnOrderAndForecast[ItemCode]))

 

Result

Best Regards,

Bof