Results 1 to 5 of 5

Thread: Rank by Date within Group
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Rank by Date within Group

    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!

  2. #2
    Board Regular
    Join Date
    Dec 2013
    Location
    Auckland, New Zealand
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank by Date within Group

    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

  3. #3
    New Member
    Join Date
    Oct 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank by Date within Group

    Quote Originally Posted by Ozeroth View Post
    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

  4. #4
    Board Regular
    Join Date
    Dec 2013
    Location
    Auckland, New Zealand
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank by Date within Group

    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.

    Code:
    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
    )

  5. #5
    New Member
    Join Date
    Oct 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank by Date within Group

    THANK YOU! That worked!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •