Problem to join 3 tables together

Question

Hello,&nbsp;Could you help me with the problem I face?I have a "Prices" table with the following structure:ItemID&nbsp;&nbsp; PriceGroup&nbsp;&nbsp; Pricen1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5n1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4n2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9n2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;CustomersPrices table:CustomerID&nbsp;&nbsp; PriceGroup001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FR002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BE&nbsp;SalesTransactions table:OrderID&nbsp;&nbsp; ItemID&nbsp;&nbsp; CustomerID&nbsp;&nbsp; QtyS01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; n1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1S01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; n2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 2S02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; n1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;I'd like to add a column to the SalesTransactions table (or a corresponding graph in the report) SalesAmount = Qty * PriceIn principle, I should be able to do it, as ItemID and CustomerID uniquely identify the price.I tried to join the tables together, but it didn't work: Ambiguos paths between SalesTransactions and Prices tables.

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
Regarding your question, I recommend that you ensure that the data set consists of multiple dimension tables and one fact table. The data you provided contains two fact tables, which is not conducive to building a star model.
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Filter criteria cannot be passed from 'SalesTransactions' to 'Prices'

You can use the 'LOOKUPVALUE' function.
LOOKUPVALUE function (DAX) - DAX | Microsoft Learn
Use the following DAX expression to create a column.
&nbsp;
SalesAmount = 
VAR _priceGroup = 
RELATED(CustomersPrices[PriceGroup])
VAR _price = 
LOOKUPVALUE('Prices'[Price],
Prices[ItemID],'SalesTransactions'[ItemID],Prices[PriceGroup],_priceGroup)
RETURN _price * [Qty]
&nbsp;

&nbsp;
Best Regards,Wenbin ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Problem to join 3 tables together

1 Reply

Related content

Scroll two tables together

Logic problem?

Join 3 fact table together with one-to-many relationship

Slider problems

FILTERING PROBLEM