Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

left join two tables both generated from DAX

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!

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    this is great! so simple and clear.  Thanks