Rank based on three different columns

[Anonymous]

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 Type	Serial No	Cycle Count	Log Date	Rank based on Serial No, Cycle Count & Fault Type	Rank based on Serial No, Log Date & Fault Type
Bent	S1256	2	10-Aug-21	1	1
Ink Failure	S1256	4	10-Sep-21	1	1
Self Stripping	S1256	8	31-Dec-21	1	1
Bent	S1256	8	31-Dec-21	2	2
Ink Failure	S1256	10	23-Feb-22	2	2
Bent	S1256	10	23-Feb-22	3	3
Broken Weld	S1256	10	23-Feb-22	1	1
Self Stripping	S1256	10	23-Feb-22	2	2
Broken Weld	S1256	10	26-Feb-22	1	2
Ink Failure	S1256	10	26-Feb-22	2	3
Self Stripping	S1256	10	26-Feb-22	2	3
Bent	S1256	10	26-Feb-22	3	4
Not Painted	S1256	10	26-Feb-22	1	1
Ink Failure	S1256	10	26-Feb-22	2	3
Bent	S1256	13	20-Apr-22	4	5
Require Stripping	S1256	13	25-Apr-22	1	1
Not Painted	S1256	13	25-Apr-22	2	2
Ink Failure	M083	1	17-Jan-22	1	1
Ink Failure	M083	1	17-Jan-22	1	1
Bent	M083	1	17-Jan-22	1	1
Bent	M083	1	17-Jan-22	1	1
Not Painted	M083	1	17-Jan-22	1	1
Not Painted	M083	1	17-Jan-22	1	1
Ink Failure	M083	3	3-Mar-22	2	2
Ink Failure	A863	1	11-Oct-21	1	1
Broken Weld	A863	1	11-Oct-21	1	1
Self Stripping	A863	1	11-Oct-21	1	1
Ink Failure	A863	1	14-Oct-21	1	2
Bent	A863	1	14-Oct-21	1	1
Ink Failure	A863	1	14-Oct-21	1	2

Appriciate for quick solutions

Thanks,

Dharani

[v-chenwuz-msft · Accepted solution]

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.

[daXtreme]

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]

@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]  
)