topic Problem to join 3 tables together in DAX Commands and Tips

Problem to join 3 tables together

Anonymous — Fri, 28 Jun 2024 12:32:17 GMT

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.

Re: Problem to join 3 tables together

Anonymous — Mon, 01 Jul 2024 02:03:05 GMT

Hi Anonymous ,

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.