topic left join two tables both generated from DAX in DAX Commands and Tips

left join two tables both generated from DAX

Anonymous — Thu, 04 May 2023 17:17:37 GMT

Hi All, i have a seemingly extremely simple left join to create a combined table in Power BI desktop

I have table 1 which all combinations of 'Month' and 'Customers' (e.g. all months by all customers), table 2 with same columns but only with months or customers that have had transactions and the 'transaction amount' as third column, i want to do a left join so that i end up all customers and all months, so that some custoemrs would then have lots of blank 'transaction amount' for the months where they haven't had any transaction.

Both tables have been created with DAX.

I tried NATRUALOUTERLEFT, all variations of SELECTCOLUMNS but to no avail. Any help would be much appreciated!

Re: left join two tables both generated from DAX

johnt75 — Fri, 05 May 2023 08:21:38 GMT

You could add a column to table 1 like

Transaction amount = LOOKUPVALUE ( 'Table 2'[Transaction amount], 'Table 2'[Month], 'Table 1'[Month], 'Table 2'[Customer], 'Table 1'[Customer] )

Re: left join two tables both generated from DAX

Anonymous — Fri, 05 May 2023 09:36:32 GMT

this is great! so simple and clear. Thanks