cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MorePowerBI
Helper II
Helper II

Need help with a calculated column that assigns sequential series of numbers for each row group

Hello,

 

I need a follow-up assistance to this recently answered questsion (@vapid128)

 

I'm trying to reproduce columns [WC#] and [WC Type] below.

 

[WC#] groups rows into sequential weight check numbers based on a grouping logic for [ValueName]:

- DispenseTipOffsets are grouped in triplets (X,Y,Z) and counted as 1 weight check.

 

- If the triplet is followed by a row where  [ValueName] = "Dispense Weight Chec", the WC# grouping should include rows until [ValueName] does not contain "Flow Rate"

 

- Rows where [ValueName] = "Dispense Weight Chec" should be grouped as 1 weight check until [ValueName] does not contain "Flow Rate"

 

[WC Type] labels [WC#] as either Tip Change or Inline. Tip Changes are the DispenseTipOffset triplets followed by the first Dispense Weight Chec and Flow Rate WC# group.

 

ValueNameCreatedDateTimeDispense #WC#WC Type
DispenseTipXOffset1/24/2022 14:18 1Tip Change
DispenseTipYOffset1/24/2022 14:18 1Tip Change
DispenseTipZOffset1/24/2022 14:18 1Tip Change
DispenseTipXOffset3/31/2022 10:25 2Tip Change
DispenseTipYOffset3/31/2022 10:25 2Tip Change
DispenseTipZOffset3/31/2022 10:25 2Tip Change
DispenseTipXOffset3/31/2022 10:49 3Tip Change
DispenseTipYOffset3/31/2022 10:49 3Tip Change
DispenseTipZOffset3/31/2022 10:49 3Tip Change
Dispense Weight Chec3/31/2022 12:0813Tip Change
Dispense Weight Chec3/31/2022 12:0823Tip Change
Dispense Weight Chec3/31/2022 12:1233Tip Change
Dispense Weight Chec3/31/2022 12:1243Tip Change
Dispense Weight Chec3/31/2022 12:1253Tip Change
Dispense Weight Chec3/31/2022 12:1263Tip Change
Dispense Weight Chec3/31/2022 12:1273Tip Change
Manual Flow Rate3/31/2022 12:12 3Tip Change
Dispense Weight Chec3/31/2022 12:2314Inline
Dispense Weight Chec3/31/2022 12:2324Inline
Dispense Weight Chec3/31/2022 12:2434Inline
Dispense Weight Chec3/31/2022 12:2444Inline
Dispense Weight Chec3/31/2022 12:2454Inline
Manual Flow Rate3/31/2022 12:24 4Inline
Dispense Weight Chec3/31/2022 13:0815Inline
Dispense Weight Chec3/31/2022 13:0825Inline
Dispense Weight Chec3/31/2022 13:0835Inline
Dispense Weight Chec3/31/2022 13:0845Inline
Dispense Weight Chec3/31/2022 13:0855Inline
Calculated Flow Rate3/31/2022 13:08 5Inline
Dispense Weight Chec3/31/2022 13:4716Inline
Dispense Weight Chec3/31/2022 13:4726Inline
Dispense Weight Chec3/31/2022 13:4736Inline
Dispense Weight Chec3/31/2022 13:4846Inline
Dispense Weight Chec3/31/2022 13:4856Inline
Calculated Flow Rate3/31/2022 13:48 6Inline

 

The JMP code for WC# is:
If(Row() == 1, 1,
(Right( Lag( :ValueName, 1 ), 4 ) == "Rate" & :ValueName != "Calculated Flow Rate") | :ValueName == "DispenseTipXOffset", Lag( :WC# ) + 1,
Lag( :WC# )
)

The JMP code for WC Type is:

If( Row() == 1 | :WC# == Lag( :WC# ) + 1,
If(
:ValueName == "DispenseTipXOffset", "Tip Change",
:ValueName == "Dispense Weight Chec", "Inline"
),
Lag( :WC Type )
)

 

Here are the PBI and data files: onedrive link

1 ACCEPTED SOLUTION
vapid128
Solution Specialist
Solution Specialist

A line Number added.
image.png

 

IsRank =
var _next = LOOKUPVALUE('Table'[ValueName],'Table'[Line Number],'Table'[Line Number]+1)
return
[ValueName] = "DispenseTipZOffset" && _next <> "Dispense Weight Chec" || RIGHT([ValueName],4)="Rate"
 
WC#2 =
RANKX(
    FILTER(
        'Table',
        'Table'[IsRank]
    ),
    [Line Number],
    ,ASC
)
 
Rank2 =
RANKX(
    FILTER(
        'Table',
        [WC#2]=EARLIER('Table'[WC#2])
    ),
    [Line Number],
    ,ASC
)
 
WC Type2 =
var FirstValueName =
LOOKUPVALUE(
    'Table'[ValueName],
    [WC#2],[WC#2],
    [Rank2],1
)
return
IF(FirstValueName="DispenseTipXOffset","Tip Change","Inline")

View solution in original post

2 REPLIES 2
vapid128
Solution Specialist
Solution Specialist

A line Number added.
image.png

 

IsRank =
var _next = LOOKUPVALUE('Table'[ValueName],'Table'[Line Number],'Table'[Line Number]+1)
return
[ValueName] = "DispenseTipZOffset" && _next <> "Dispense Weight Chec" || RIGHT([ValueName],4)="Rate"
 
WC#2 =
RANKX(
    FILTER(
        'Table',
        'Table'[IsRank]
    ),
    [Line Number],
    ,ASC
)
 
Rank2 =
RANKX(
    FILTER(
        'Table',
        [WC#2]=EARLIER('Table'[WC#2])
    ),
    [Line Number],
    ,ASC
)
 
WC Type2 =
var FirstValueName =
LOOKUPVALUE(
    'Table'[ValueName],
    [WC#2],[WC#2],
    [Rank2],1
)
return
IF(FirstValueName="DispenseTipXOffset","Tip Change","Inline")

Wow - I just want to keep you in my pocket. Thanks for the help!

 

IsRank =
var _next = LOOKUPVALUE(WeightCheck[ValueName],WeightCheck[Index],WeightCheck[Index]+1)
return
[ValueName] = "DispenseTipZOffset" && _next <> "Dispense Weight Chec" || RIGHT([ValueName],4)="Rate" && RIGHT(_next,4)<>"Rate"

I added the fourth condition so that any consecutive rows with [ValueName] ending with "Rate" are counted towards the same WC#

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors