cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mpowerrajesh365
Helper I
Helper I

Multiple table join with conditions

Hi I have a quick question if somebody can help please!!

 

I have a table1 with columns display number, order number fields..

I have another table2 with order number field..

Now I want to join these two tables with a condition...like below..

First I need to check if table2 order number starts with xy then I need to join that with display number...if not then I need to join with order number...

 

Thanks for your help

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@mpowerrajesh365 you cannot have conditional join, what is your end goal?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

5 REPLIES 5
mpowerrajesh365
Helper I
Helper I

Yes, you data is right. I wanted to acheive this in power query rather than in measure. 

but thanks for your help...

v-yangliu-msft
Community Support
Community Support

Hi  @mpowerrajesh365  ,

According to your description, I create this data:

Table1:

v-yangliu-msft_0-1613536511626.png

Table2:

v-yangliu-msft_1-1613536511629.png

Here are the steps you can follow:

1. Create measure.

Measure = 
IF(
    LEFT(MAX('Table2'[order number]),2)="xy",
    CALCULATE(MAX('Table1'[display number]),FILTER(ALL('Table1'),[order number]=MAX([order number]))),
    CALCULATE(MAX('Table1'[order number]),FILTER(ALL('Table2'),[order number]=MAX([order number])&&[order date]>DATEADD('Table1'[created],-1,MONTH)))
    )

2. Result

v-yangliu-msft_2-1613536511631.png

You can downloaded PBIX file from here.

 

If my answer is not what you want,can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

 

Best Regards,

Liu Yang

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

parry2k
Super User III
Super User III

@mpowerrajesh365 it will be easier if you throw sample data and expected output.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





mpowerrajesh365
Helper I
Helper I

I have two columns from which I need to identify which column I need to join to. Basically I am trying to get the below dax converted into power query 

IF(LEFT('Table1'[OrderNumber],2) = "XY", Table2[quote_number],
Table2[order_number]) = Table1[OrderNumber] && table2[order_date] > DATEADD('table1'[CREATED],-1,MONTH)

parry2k
Super User III
Super User III

@mpowerrajesh365 you cannot have conditional join, what is your end goal?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.