Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
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.
@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
⭕ 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.
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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
even itis not giving ground when we have different ISW. Thanks for the help
Another example
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])
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
10 | |
10 | |
8 | |
7 |