Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Hi Ashish,
Thanks a ton for your help.
Thanks,
Dharani
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |