Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am attemping to rank date/time values by a subcategory called Incident ID.
The DAX to add this data to your model is the following calculated table:
Table = DATATABLE( "IncidentID" , STRING , "EquipmentType" , STRING, "TimeArrived" , DATETIME, { {"15-53","Pumper","2015-05-15 07:45:15"}, {"16-01","Pumper","2016-03-25 14:45:08"}, {"16-01","Truck","2016-03-25 14:46:10"}, {"16-01","Pumper","2016-03-25 14:50:11"}, {"16-01","Squad","2016-03-25 14:50:08"}, {"18-86","Truck","2018-08-11 12:23:15"}, {"18-86","Pumper","2018-08-11 12:26:10"}, {"18-86","Unit",}, {"17-12","Truck","2017-02-28 10:00:00"}, {"17-12","Pumper","2017-02-28 10:02:16"}, {"17-12","Squad","2017-02-28 10:03:20"}, {"17-12","Pumper","2017-02-28 10:06:16"}, {"17-12","Truck",}, {"17-12","Unit",} } )
I have made progress with the following DAX expression, but cannot seem to figure out how to deal with blank (null) time values. Here is my current expression I am using:
RankOnScene = RANKX( FILTER( CAD_Vehicles, CAD_Vehicles[Incident Number] = EARLIER(CAD_Vehicles[Incident Number])), CAD_Vehicles[Time ArrivedAtScene],,ASC,Dense)
However, this results in ranking values as blank as #1 in their subcategory, which you can see in the table below:
IncidentID | EquipmentType | TimeArrived | ArrivedRanking |
15-53 | Pumper | 5/15/15 7:45 AM | 1 |
16-01 | Pumper | 3/25/16 2:45 PM | 1 |
16-01 | Truck | 3/25/16 2:46 PM | 2 |
16-01 | Pumper | 3/25/16 2:50 PM | 3 |
16-01 | Squad | 3/25/16 2:51 PM | 4 |
18-86 | Pumper | 8/11/18 12:23 AM | 2 |
18-86 | Truck | 8/11/18 12:27 AM | 3 |
18-86 | Unit | 1 | |
17-12 | Truck | 2/28/17 10:00 AM | 2 |
17-12 | Pumper | 2/28/17 10:02 AM | 3 |
17-12 | Squad | 2/28/17 10:03 AM | 4 |
17-12 | Pumper | 2/28/17 10:06 AM | 5 |
17-12 | Truck | 1 | |
17-12 | Unit | 1 |
I would like to see the blank values ignored when ranking date/time values, which would then look like this:
Desired Output | |||
IncidentID | EquipmentType | TimeArrived | ArrivedRanking |
15-53 | Pumper | 5/15/15 7:45 AM | 1 |
16-01 | Pumper | 3/25/16 2:45 PM | 1 |
16-01 | Truck | 3/25/16 2:46 PM | 2 |
16-01 | Pumper | 3/25/16 2:50 PM | 3 |
16-01 | Squad | 3/25/16 2:51 PM | 4 |
18-86 | Pumper | 8/11/18 12:23 AM | 1 |
18-86 | Truck | 8/11/18 12:27 AM | 2 |
18-86 | Unit | ||
17-12 | Truck | 2/28/17 10:00 AM | 1 |
17-12 | Pumper | 2/28/17 10:02 AM | 2 |
17-12 | Squad | 2/28/17 10:03 AM | 3 |
17-12 | Pumper | 2/28/17 10:06 AM | 4 |
17-12 | Truck | ||
17-12 | Unit |
I would like to avoid doing a manual filter on blank date/time values because there are other columns in my table that have needed important information I'm using in separate operations.
Any advice is greatly appreciated!
BONUS:
It would be useful to rank by an additional subcategory, which is equipment type. So for similar pieces of equipment having the same Incident ID, which arrived first? In that case, the desired output would look like this:
16-01 | Pumper | 3/25/16 2:45 PM | 1 |
16-01 | Truck | 3/25/16 2:46 PM | 1 |
16-01 | Pumper | 3/25/16 2:50 PM | 2 |
16-01 | Squad | 3/25/16 2:51 PM | 1 |
Thank you very much!!
Solved! Go to Solution.
@Anonymous Please try this...
Rank = VAR _Rnk = RANKX(FILTER(Test45Ranking,Test45Ranking[IncidentID]=EARLIER(Test45Ranking[IncidentID]) && Test45Ranking[TimeArrived] <> BLANK()),Test45Ranking[TimeArrived],,ASC,Dense) RETURN IF(Test45Ranking[TimeArrived]=BLANK(),BLANK(),_Rnk)
Proud to be a PBI Community Champion
@Anonymous Here it is the solution for Bonus question
BonusRank = VAR _Rnk = RANKX(FILTER(Test45Ranking,Test45Ranking[IncidentID]=EARLIER(Test45Ranking[IncidentID]) && Test45Ranking[EquipmentType] = EARLIER(Test45Ranking[EquipmentType]) && Test45Ranking[TimeArrived] <> BLANK()),Test45Ranking[TimeArrived],,ASC,Dense) RETURN IF(Test45Ranking[TimeArrived]=BLANK(),BLANK(),_Rnk)
Proud to be a PBI Community Champion
@Anonymous Here it is the solution for Bonus question
BonusRank = VAR _Rnk = RANKX(FILTER(Test45Ranking,Test45Ranking[IncidentID]=EARLIER(Test45Ranking[IncidentID]) && Test45Ranking[EquipmentType] = EARLIER(Test45Ranking[EquipmentType]) && Test45Ranking[TimeArrived] <> BLANK()),Test45Ranking[TimeArrived],,ASC,Dense) RETURN IF(Test45Ranking[TimeArrived]=BLANK(),BLANK(),_Rnk)
Proud to be a PBI Community Champion
Very good. Thank you so much.
@Anonymous Please try this...
Rank = VAR _Rnk = RANKX(FILTER(Test45Ranking,Test45Ranking[IncidentID]=EARLIER(Test45Ranking[IncidentID]) && Test45Ranking[TimeArrived] <> BLANK()),Test45Ranking[TimeArrived],,ASC,Dense) RETURN IF(Test45Ranking[TimeArrived]=BLANK(),BLANK(),_Rnk)
Proud to be a PBI Community Champion
@PattemManohar could you provide some advise on this here please https://community.powerbi.com/t5/Power-Query/RANKX-excluding-blanks/m-p/805159#M27006
That worked! Thank you so much!
By creating a variable that is only called if the row value for time arrived is not blank, you are excluding those values from the earlier operation when ranking. Am I understanding that correctly?
Thank you again for your help.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |