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.
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.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |