Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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 Table1
Expected 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)
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 Table1
Expected 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).
User | Count |
---|---|
101 | |
91 | |
83 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |