Select value from another Table for operation.

[Anonymous]

Hello All,

I have two tables as below.Have not listed all the columns in them.I need to get the price from the Price table for each of the part in the Order table and create a new column with that.Merging the tables would work but is there any other way?

Thanks for helping

Part	Order	Qty	New Column(QTY*Price
123	ABC	5	5
345	XYZ	6	12
678	PQR	7	21

Part	Price
123	1
345	2
678	3
234	4
567	5

Thanks

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calculated column in Order table as below.

Column =
VAR pri =
    CALCULATE (
        MAX ( Price[Price] ),
        FILTER ( Price, Price[Part ] = 'Order'[Part] )
    )
RETURN
    'Order'[Qty] * pri

Pbix as attached.

[Anonymous]

@v-frfei-msft Thanks.That was helpful and worked.

[parry2k]

@Anonymous assuming you have relationship between these two tables, Part table is on one side of the relationship, you can use expression to add new calculated column in your table

New Column Qty x Price = 
Table[Qty] * RELATED ( Part[Price] )

[Anonymous]

@parry2k Thanks.There is a many to many relationship and guess Related does not work well with this relationship as i get error.

The column  either doesn't exist or doesn't have a relationship to any table available in the current context.

[parry2k]

@Anonymous many to many is surely the issue, not sure why you have many records for a part number in part table.