Forum Discussion

salmankhan_hm's avatar
salmankhan_hm
Frequent Visitor
4 years ago

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

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.

 

Need Help
Show and Tell

7 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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])

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      salmankhan_hm
      Frequent Visitor
      4 years ago

      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's avatar
        Fowmy
        Icon for Super User rankSuper User
        4 years ago

        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 


  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.