Multiple table join with conditions

[Anonymous]

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

[Anonymous]

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]

Hi  @Anonymous  ,

According to your description, I create this data：

Table1：

Table2：

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

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]

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

[Anonymous]

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 · Accepted solution]

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