# Rank by Date within Group



## amulder (Jan 24, 2017)

I am trying to create a DAX calculation in PowerPivot that will give me a rank based upon a date within a group.  Here is a sample of my data:

Event#  Unit  Arrive_Time
1234     100  2017-01-01 10:00:00
1234     102  2017-01-01 10:01:00
2345     100  2017-01-01 10:05:00
2345     102  2017-01-01 10:04:00

My desired results would show the following:

Event#  Unit  Arrive_Time                 Arrival_Order
1234     100  2017-01-01 10:00:00      1
1234     102  2017-01-01 10:01:00      2
2345     100  2017-01-01 10:05:00      2
2345     103  
2345     102  2017-01-01 10:04:00      1

The Arrival_Order is what I am trying to achieve but have been unsuccessful using both the Rankx and the countrows functions as I can't seem to get the numbering to reset based on Event Number.  I will also have scenarios that will not include an Arrive_Time at all.  In these cases I don't want it included in the Arrival_Order.

Any help is appreciated.

Thanks!


----------



## Ozeroth (Jan 24, 2017)

Hi amulder,

Have a look at this article - it shows how you can create a sequence column using DAX, which resets based on a particular column:
Numbering sequence of events in DAX - SQLBI


----------



## amulder (Jan 26, 2017)

Ozeroth said:


> Hi amulder,
> 
> Have a look at this article - it shows how you can create a sequence column using DAX, which resets based on a particular column:
> Numbering sequence of events in DAX - SQLBI




Hi,

Thanks for this, I still am not able to get it working.  Are you able to provide the syntax using my column names in this scenario?

Thanks,

Ashlee


----------



## Ozeroth (Jan 26, 2017)

Hi again,

Something like this should work.
I called your table Data.
It is a slight modification of the SQLBI version, modified to ignore blanks and base the Sequence # at 1. Also your table has a DateTime column whereas SQLBI used separate Date & Time columns.


```
Sequence by Event # = 
IF (
    NOT ( ISBLANK ( Data[Arrive_Time] ) ),
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                Data,
                ALLEXCEPT ( Data, Data[Event#] ),
                NOT ( ISBLANK ( Data[Arrive_Time] ) )
            ),
            Data[Arrive_Time] < EARLIER ( Data[Arrive_Time] )
        )
    )
        + 1
)
```


----------



## amulder (Jan 27, 2017)

THANK YOU!  That worked!


----------

