Forum Discussion

MorePowerBI's avatar
MorePowerBI
Helper II
4 years ago
Solved

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 che...
calculated column
DAX
Need Help
  • vapid128's avatar
    vapid128
    4 years ago

    A line Number added.

     

    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")
vapid128's avatar
vapid128
Solution Specialist
4 years ago

A line Number added.

 

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")
MorePowerBI's avatar
MorePowerBI
Helper II
4 years ago

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#