Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rank based on three different columns

Hi BI Experts,

 

Need help here to get solution of "Rank based on Serial No, Cycle Count & Fault Type" & "Rank based on serial No, Log Date and Fault Type". With calculated column(DAX) or Power Query is fine.

 

 

Rank based on Serial No, Cycle Count & Fault Type = Count of distinct Cycles when similar fault Type Occur

Rank based on serial No, Log Date and Fault Type = Count of distinct last different log Date when similar fault type logged

 

 

 

Fault TypeSerial NoCycle CountLog DateRank based on Serial No, Cycle Count & Fault TypeRank based on Serial No, Log Date & Fault Type
BentS1256210-Aug-2111
Ink FailureS1256410-Sep-2111
Self StrippingS1256831-Dec-2111
BentS1256831-Dec-2122
Ink FailureS12561023-Feb-2222
BentS12561023-Feb-2233
Broken WeldS12561023-Feb-2211
Self StrippingS12561023-Feb-2222
Broken WeldS12561026-Feb-2212
Ink FailureS12561026-Feb-2223
Self StrippingS12561026-Feb-2223
BentS12561026-Feb-2234
Not PaintedS12561026-Feb-2211
Ink FailureS12561026-Feb-2223
BentS12561320-Apr-2245
Require StrippingS12561325-Apr-2211
Not PaintedS12561325-Apr-2222
Ink FailureM083117-Jan-2211
Ink FailureM083117-Jan-2211
BentM083117-Jan-2211
BentM083117-Jan-2211
Not PaintedM083117-Jan-2211
Not PaintedM083117-Jan-2211
Ink FailureM08333-Mar-2222
Ink FailureA863111-Oct-2111
Broken WeldA863111-Oct-2111
Self StrippingA863111-Oct-2111
Ink FailureA863114-Oct-2112
BentA863114-Oct-2111
Ink FailureA863114-Oct-2112

 

 

Appriciate for quick solutions

 

Thanks,

Dharani

 

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ，

     

    Please try this code:

    Rank based on Serial No, Cycle Count & Fault Type =
RANKX (
    FILTER (
        'Table',
        [Fault Type] = EARLIER ( 'Table'[Fault Type] )
            && [Serial No] = EARLIER ( 'Table'[Serial No] )
    ),
    [Cycle Count],
    ,
    ASC,
    DENSE
)

Rank based on Serial No, Log Date & Fault Type =
RANKX (
    FILTER (
        'Table',
        [Fault Type] = EARLIER ( 'Table'[Fault Type] )
            && [Serial No] = EARLIER ( 'Table'[Serial No] )
    ),
    [Log Date],
    ,
    ASC,
    DENSE
)

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    This is a bit too vague to give you a definite answer. Please make it more descriptive, and best show calculations in slow motion. Thanks.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 

    Not sure what expression you need to rank by, please use the following appraoch and modify the code as necessary

    Rank 1 = 
VAR __FAULT=Table4[Fault Type]
VAR __SN= Table4[Serial No]
RETURN
RANKX(
    FILTER( Table4 , Table4[Fault Type] = __FAULT && Table4[Serial No] = __SN ) ,
    Table4[Cycle Count]  
)

     

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ，

     

    Please try this code:

    Rank based on Serial No, Cycle Count & Fault Type =
RANKX (
    FILTER (
        'Table',
        [Fault Type] = EARLIER ( 'Table'[Fault Type] )
            && [Serial No] = EARLIER ( 'Table'[Serial No] )
    ),
    [Cycle Count],
    ,
    ASC,
    DENSE
)

Rank based on Serial No, Log Date & Fault Type =
RANKX (
    FILTER (
        'Table',
        [Fault Type] = EARLIER ( 'Table'[Fault Type] )
            && [Serial No] = EARLIER ( 'Table'[Serial No] )
    ),
    [Log Date],
    ,
    ASC,
    DENSE
)

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.