RANKX by Subcategory and Exclude Blanks

[Anonymous]

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!!

[PattemManohar · Accepted 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)

[PattemManohar · Accepted solution]

@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)

[Anonymous]

Very good. Thank you so much. 

[PattemManohar · Accepted 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)

[CAPEconsulting]

@PattemManohar could you provide some advise on this here please https://community.powerbi.com/t5/Power-Query/RANKX-excluding-blanks/m-p/805159#M27006

[Anonymous]

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.