Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Join on multiple columns using Power query

HI ,   I need to achieve join condition in Power BI similar to sql joins that was generated from other tool. Below is my Join condition that was generated by the tool on the two tables(Sales and Pu...
Need Help
Tips and Tricks
  • edhans's avatar
    edhans
    6 years ago

    Anonymous I think you might be trying to apply the logic of some other app you are using to Power BI, and that will not work. You don't actually create JOINS in the Power BI data model. You do in Power Query, but not in DAX. In DAX they are filter relationships, and they either equal or they don't.

     

    However, once you have a filter relationship, you can apply other logic in your formulas (measures) to modify how they work. For example, you might have the following to show cumulative sales through a date on your visual:

    Sales Cumulative =
VAR varCurrentDate =
    MAX( Sales[Date] )
RETURN
    CALCULATE(
        [Sales],
        Date[Date] <= varCurrentDate,
        REMOVEFILTERS( Dates[Date] )
    )

    That will cause the filter to act in a way that will get all sales prior to today through today.

     

    But that is not a merge operation.

     

    Does that make sense? 

    Perhaps tell us what your end goal is, and not ask us to translate Program A logic into Power BI logic. It can cause us to come at this totally wrong, which I've certianly being doing so far. 😁

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks swise001  for the response.

This is helpful as a part of my requirement. My other scenario in this query is how to achieve comparison of different columns as shown below

 

purchase.pur_id (+)= Sales.numid and
sales.pcode=purchase.mid(+) and
purchase.frm__dt (+)<= sales.dum_dt and
purchase.to_dt (+) >= sales.dum_dt and
purchase.ven_id (+)||purchase.fixd is not null

 

I will do a concatenate on two columns as you suggested.

 

purchase.pur_id (+)= Sales.numid and
sales.pcode=purchase.mid(+) and

 

But how to achieve the below requirement in Power query.

 

purchase.frm__dt (+)<= sales.dum_dt and
purchase.to_dt (+) >= sales.dum_dt

 

I need to create a calculated column on the above two conditions and use that to create a relationship between this tables to filter data.

 

Any help/approach will be highly appreciated. 

 

Thanks in advance.

swise001's avatar
swise001
Continued Contributor
6 years ago

Anonymous 

 

My suggestion would be to first do the merge and bring over any columns that will be necessary with your conditional logic.  

Then you can add a conditional column where you use this comparitive criteria to 'flag' only those items that meet the criteria.  

 

Consider breaking it down into small steps (since it's got both AND and OR criteria) - so you may need a few conditional columns to make it work.  

You can also write AND/OR logic into the Add Custom Column box using a format like this: 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks swise001  for the response

     

    My sales fact table has relationship with multiple tables along with Purchase dim table. So i dont want to go and create multiple merge tables and use it in my model and create reports on top of it.

     

    As my fact table is surrounded by multple dimension tables, I am looking for a better approach to resolve the join condition between the sales table and with the individual tables using the concatenate columns.

     

     

    So as a first step, i am trying to replicate the join condition that occurs between the fact(Sales) and Purchase table.Like wise i am planning to do the same with fact and other dim tables. so that relation between dim and fact table occurs as 1:M and the data gets filtered accordingly.

     

    Please correct me if my approach is wrong in doing so.

     

    As part of impementation between the tables, I am unable to figure how to implement this logic from 2 tables

     

    purchase.frm__dt (+)<= sales.dum_dt and

    purchase.to_dt (+) >= sales.dum_dt

     

    I tried using lookupvalue but unable to achieve on Dim table.

     

    Any suggestion will be highly appreciated.

     

    Thanks in advance.