Filter condition and return to a new table

[NickProp28]

Dear Community,

Would like to use CALCULATETABLE to create a new table based on this main table. Need you guys help me on filter part.

If one job appear more than one time, the job which have MIN(ATD), MAX(Actual Felivery Date) and MAX (Posted) date will be selected. Only return one row for each of the job.

Example for B002, Peter job. Since there have complexity date, will priority on the row which having MIN(ATD). (12 March 2020)

Furthermore, if the column in null, will be consider is the lowerst date. 

Here is the pbix file: https://ufile.io/k1pj70ah

Thanks for your precious time on this matter.

[Greg_Deckler · Accepted solution]

@NickProp28 - I would use SUMMARIZE or SUMMARIZECOLUMNS and not CALCULATETABLE. Something like:

Table =
  SUMMARIZE(
    'Table',
    [Job],
    "ATD",MIN([ATD]),
    "Actual Felivery Date",MAX([Actual Felivery Date]),
    "Posted",MAX([Posted])
  )

[amitchandak · Accepted solution]

@NickProp28 , You need use summarize like

summarize ( Table, Table[Job], , Table[Operator], Table[Direction], Table[IncoTerms] ,"ATD" ,MIN(ATD), "Actual Delivery Date" , MAX(Actual Delivery Date), "Posted" MAX (Posted) )

or

summarize ( Table, Table[Job], , Table[Operator], Table[Direction],"ATD" ,MIN(ATD), "Actual Delivery Date" , MAX(Actual Delivery Date), "Posted" MAX (Posted) )

or

summarize ( Table, Table[Job], , Table[Operator] ,"ATD" ,MIN(ATD), "Actual Delivery Date" , MAX(Actual Delivery Date), "Posted" MAX (Posted) )

//depending on the need you can add remove group by and columns

[NickProp28]

Dear @amitchandak , @Greg_Deckler 

Thanks for your attention and guide for my issues.

[Greg_Deckler · Accepted solution]

@NickProp28 - I would use SUMMARIZE or SUMMARIZECOLUMNS and not CALCULATETABLE. Something like:

Table =
  SUMMARIZE(
    'Table',
    [Job],
    "ATD",MIN([ATD]),
    "Actual Felivery Date",MAX([Actual Felivery Date]),
    "Posted",MAX([Posted])
  )