Problem to join 3 tables together

[avilovi]

Hello,

Could you help me with the problem I face?

I have a "Prices" table with the following structure:

ItemID   PriceGroup   Price

n1          FR                 5

n1          BE                 4

n2          FR                 9

n2          BE                 8

CustomersPrices table:

CustomerID   PriceGroup

001                FR

002                BE

SalesTransactions table:

OrderID   ItemID   CustomerID   Qty

S01          n1          001                1

S01          n2          001                2

S02          n1          002                1

I'd like to add a column to the SalesTransactions table (or a corresponding graph in the report) SalesAmount = Qty * Price

In 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 solution]

Hi @avilovi ,

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.

SalesAmount = 
VAR _priceGroup = 
RELATED(CustomersPrices[PriceGroup])
VAR _price = 
LOOKUPVALUE('Prices'[Price],
Prices[ItemID],'SalesTransactions'[ItemID],Prices[PriceGroup],_priceGroup)
RETURN _price * [Qty]

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.