How to compare rows and create a new measure or calculated column?

[salmankhan_hm]

Hi All,

Hope you are doing well!

I am having some data and want to create a new column by comparing the rows. If the order number and season is the same , the one with the earliest  ISW is ground, if the same ISW; then the largest qty defines ground/trail.

The final table should be like this

Thanks.

[v-yanjiang-msft]

Hi @salmankhan_hm ,

Have you tried @wdx223_Daniel 's solution? By my test, it can get correct result, even though for the single record Order Num200242.

I also attach the sample for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Fowmy]

@salmankhan_hm 

Add the following calculated column in your table:

Ground/Trail = 
VAR __O = Table1[Order Nur]
VAR __S = Table1[Season]
VAR __FTR = FILTER( Table1 , Table1[Order Nur] = __O && Table1[Season] = __S)
VAR __MINDATE =  MINX(__FTR,Table1[ISW Date])
VAR __FTRDATE = FILTER(__FTR, Table1[ISW Date] = __MINDATE)
VAR __MINDATECOUNT = COUNTROWS(__FTRDATE)
VAR __MAXPCS = MAXX( __FTRDATE  , Table1[Ordered Pcs])
VAR __RESULT = 
SWITCH(
    TRUE(),    
    __MINDATECOUNT = 1 && 'Table1'[ISW Date] = __MINDATE , "Ground",  
    __MINDATECOUNT > 1 &&  'Table1'[Ordered Pcs] = __MAXPCS , "Ground",
            "Trail"
    )
RETURN
   __RESULT

[salmankhan_hm]

Thanks for the solution but what if the order is only one I mean just one row. Right now the order with no matching is giving me Trail and for only one order it should be Ground.

Thanks in Advance. 

[Fowmy]

@salmankhan_hm 

If the you have a single row for particular order, you should always get Ground as the result and it works.
Sorry I didn't get "Right now the order with no matching is giving me Trail ", please show me an example 

[salmankhan_hm]

even itis not giving ground when we have different ISW. Thanks for the help

[salmankhan_hm]

Another example

[wdx223_Daniel]

In DAX

CalculatedColumn=IF(MAXX(TOPN(1,FILTER(Table,Table[Order Num]=EARLIER(Table[Order Num])&&Table[Season]=EARLIER(Table[Season])),Table[ISW Date],ASC),Table[Ordered Pcs])=Table[Ordered Pcs],"Ground","Trail")

with the same qtys will get many Ground

in PowerQuery

NewStep=Table.Combine(Table.Group(PreviousStepName,{"Order Num","Season"},{"n",each let a=Table.Sort(_,{{"ISW Date",0},{"Ordered Pcs",1}}) in Table.FromColumns(Table.ToColumns(a)&{{"Ground"}&List.Repeat({"Trail"},Table.RowCount(a)-1)},Table.ColumnNames(a)&{"Ground/Trial"})})[n])