Forum Discussion
Complicated case
Hi everyone,
I have two tables, and I'm looking for matching Table1's rows with table2's rows like I'm showing on the table below
Table1:
| Order N | Line N | Delivery Date |
| L12 | 1 | 15/01/2017 |
| L12 | 7 | 17/02/2017 |
| L13 | 1 | 02/03/2017 |
| L14 | 2 | 20/03/2017 |
| L15 | 4 | 19/05/2017 |
| L15 | 5 | 01/02/2017 |
| L14 | 3 | 01/02/2017 |
| L20 | 1 | 08/08/2017 |
Table2 :
| Order N | Line N | Expected DelivDate |
| L12 | 1 | 21/08/2017 |
| L12 | 7 | 22/08/2017 |
| L13 | 1 | 23/08/2017 |
| L14 | 3 | 24/08/2017 |
| L11 | 2 | 25/08/2017 |
| L15 | 4 | |
| L15 | 5 | 24/08/2017 |
| L14 | 5 | 24/08/2017 |
| L1 | 2 | |
| L21 | 2 | 01/08/2017 |
| L30 | 1 | |
| L12 | 1 | 24/08/2017 |
Result :
| Order N | Line N | Delivery Date | Expected DelivDate |
| L12 | 1 | 15/01/2017 | 24/08/2017 |
| L12 | 7 | 17/02/2017 | 22/08/2017 |
| L13 | 1 | 02/03/2017 | 23/08/2017 |
| L14 | 2 | 20/03/2017 | |
| L15 | 4 | 19/05/2017 | |
| L15 | 5 | 01/02/2017 | 24/08/2017 |
| L14 | 3 | 01/02/2017 | 24/08/2017 |
| L20 | 1 | 08/08/2017 |
I used this code as a solution:
TableResult = SUMMARIZE(
GENERATE(
Table1; Var OrderNum = 'Table1'[Order N]
Var LineNum = 'Table2'[Line N]
return FILTER('Table2'; 'Table2'[Order N]=OrderNum && 'Table2'[Line N]=LineNum )
);
Table2[Order N]; Table2[Line N];
"Delivery Date"; MAX(Table2[Expected DelivDate])
)
But that doesn't really work.
Someone can help me please.
does it have to be single formula?
why can't you add a calculated column with Expected Delivery Date to Table1?
Also - are you creating this table to calculate time difference only? if so this should be possible to achieve with a measure, without creating a new table
EDIT - this calculated formula witll work when added to Table1Expected Delivery Date= VAR CurrentOrder = Table1[Order N] VAR CurrentLine = Table1[Line N] RETURN CALCULATE(MAX(Table2[Expected DelivDate]),Table2[Order N]=CurrentOrder,Table2[Line N]=CurrentLine)
2 Replies
does it have to be single formula?
why can't you add a calculated column with Expected Delivery Date to Table1?
Also - are you creating this table to calculate time difference only? if so this should be possible to achieve with a measure, without creating a new table
EDIT - this calculated formula witll work when added to Table1Expected Delivery Date= VAR CurrentOrder = Table1[Order N] VAR CurrentLine = Table1[Line N] RETURN CALCULATE(MAX(Table2[Expected DelivDate]),Table2[Order N]=CurrentOrder,Table2[Line N]=CurrentLine)
Thank you a lot for the response;
It's more useful for my case;
And you are right, I want to make some measures, but not directly on the Expected Delivery date and the Delivery date. I should create two other columns :
Column1 = If(Delivery Date - Expected DelivDate >7;0;1)Calumn2= If(Delivery Date - Estimate DelivDate>7;0;1)
Estimate DelivDate is estimated by our Service and the expected DelivDate is estimated by our supplier.
After that, I'll add a third column which is the max (Column1;Column2) and calculate the percentage of the sum(Calumn3)Divided by count(Orders).
