Forum Discussion
Rank for 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 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
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.
3 Replies
- Ashish_Mathur
Super User
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.
- Ashish_Mathur
You are welcome.