Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
salmankhan_hm
Frequent Visitor

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.

salmankhan_hm_0-1661329060745.png
The final table should be like this

salmankhan_hm_1-1661329192756.png


Thanks.

 

7 REPLIES 7
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1661851042123.png

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
Super User
Super User

@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

Fowmy_0-1661332266698.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.

salmankhan_hm_0-1661350445730.png


Thanks in Advance. 

@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 


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

salmankhan_hm_0-1661354282036.png

 

salmankhan_hm_1-1661354322922.png

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

salmankhan_hm_2-1661354483200.png

Another example

wdx223_Daniel
Super User
Super User

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors