Rank for 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

Thanks,

Dharani

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Rank based on Serial No, Cycle Count & Fault Type = CALCULATE(DISTINCTCOUNT(data[Cycle Count]),FILTER(data,data[Serial No]=EARLIER(data[Serial No])&&data[Fault Type]=EARLIER(data[Fault Type])&&data[Log Date]<=EARLIER(data[Log Date])))

Rank based on Serial No, Log Date & Fault Type = CALCULATE(DISTINCTCOUNT(data[Log Date]),FILTER(data,data[Serial No]=EARLIER(data[Serial No])&&data[Fault Type]=EARLIER(data[Fault Type])&&data[Log Date]<=EARLIER(data[Log Date])))

Hope this helps.

[Anonymous]

Hi Ashish,

Thanks a ton for your help.

Thanks,

Dharani

[Ashish_Mathur]

You are welcome.