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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors