Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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 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] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
@Anonymous many to many is surely the issue, not sure why you have many records for a part number in part table.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |