locked
Rank - Power Pivot Calculated Column (Rank within Unique ID) RRS feed

  • Question

  • Hi,

    I've read the top related threads, but all are a bit different than what I'm after (that I found). I think I know how to do this within a measure (following Rob Collie's football example), but unsure with a calculated column. 

    My unique ID is my "incidentkey". I have one "incidentkey" in the incident table which represent an emergency call.  My "apparatus table" contains a list of all units that responds to each emergency. As an example, if we respond to a fire alarm @ 12345 Main Street for a building fire, I will have one incident key in the incident table. In the apparatus table, it will list all the units that responded to the fire alarm. Thanks to Michael Amadi (Response Time Measure) I'm able to calculate if we meet our response time goals, but I'm adding a little more detail,  which requires me to rank the order the units arrive on each incident. 

    Is it possible to RANK to order in which the units arrived in my Apparatus table based off of the unique Incidentkey with a calculated column? The RANK would be based off the ArrivalDate column. 

    Below is my data model and an example of my apparatus table. 

    Thank you, Brent



    • Edited by bvanscoy678 Monday, October 20, 2014 1:31 PM
    Monday, October 20, 2014 1:19 PM

Answers

  • RankX would be a better solution.  If you want the blank dates to be ranked last then use the following:

    =RANKX(FILTER(Apparatus, [IncidentKey] = EARLIER([IncidentKey])), [ArrivalDate])

    Or similar to what you are trying to achieve above by blanking the blanks

    =IF(ISBLANK([ArrivalDate]), BLANK(), RANKX(FILTER(Apparatus, [IncidentKey] = EARLIER([IncidentKey])), [ArrivalDate]))

    Regards

    Laurence

    • Proposed as answer by greggyb Tuesday, October 21, 2014 2:49 PM
    • Marked as answer by bvanscoy678 Thursday, October 23, 2014 12:23 AM
    Tuesday, October 21, 2014 12:51 PM

All replies

  • Hello,

    This calculated column would do the job:

    =COUNTROWS(FILTER(Apparatus; [IncidentKey] = EARLIER([IncidentKey]) && [ArrivalDate] < EARLIER([ArrivalDate]))) + 1

    Bye

    Monday, October 20, 2014 6:24 PM
  • Hi,

    I had to change the semicolon in the Filter(Apparatus,....) but I think that's a version difference in Excel. 

    =COUNTROWS(FILTER(Apparatus, [IncidentKey] = EARLIER([IncidentKey]) && [ArrivalDate] < EARLIER([ArrivalDate]))) + 1

    That's did the trick! I'll have to do a little more work because it highlighted an issue I did not anticipate, which is a blank arrival date (either they did not arrive or their times were missing). Maybe I can wrap the above formula in an If statement to test for missing time. 

    Thank you for the help!

    Brent


    Monday, October 20, 2014 8:40 PM
  • Hello,

    I added the below wrapper, but it just continues to cycle without completing. Any suggestions?

    Thanks

    =if(ISBLANK([arrivaldate]),Blank(),
                                     COUNTROWS(FILTER(apparatus,[incidentkey]=EARLIER([incidentkey]) &&
                                                          [arrivaldate] <EARLIER([arrivaldate]))) +1)

    Monday, October 20, 2014 11:44 PM
  • RankX would be a better solution.  If you want the blank dates to be ranked last then use the following:

    =RANKX(FILTER(Apparatus, [IncidentKey] = EARLIER([IncidentKey])), [ArrivalDate])

    Or similar to what you are trying to achieve above by blanking the blanks

    =IF(ISBLANK([ArrivalDate]), BLANK(), RANKX(FILTER(Apparatus, [IncidentKey] = EARLIER([IncidentKey])), [ArrivalDate]))

    Regards

    Laurence

    • Proposed as answer by greggyb Tuesday, October 21, 2014 2:49 PM
    • Marked as answer by bvanscoy678 Thursday, October 23, 2014 12:23 AM
    Tuesday, October 21, 2014 12:51 PM
  • Laurence,

    Yes, the second one worked perfect. Because they don't arrive, I would rather not give them a rank as if they did. 

    Thank you both for the help on this solution!! I can take this information and build upon it with identifying the first Engine (water), the first Truck (ladder) and the firsts Medic unit. 

    Again, it is greatly appreciated. 

    Thanks


    Brent

    Tuesday, October 21, 2014 2:24 PM
  • Hi,

    The calculation works perfect, but I'm still working through how it works. I understand the RANKX and using Filter() allows the calculated column to have row context. 

    FILTER(Apparatus, [IncidentKey] = EARLIER([IncidentKey])

    This is my table(of the RANKX() ). Do I understand correctly that you are comparing the IncidentKey in this row to the IncidentKey in the previous row (EARLIER() )? And then it will RANK the values in the [Arrival Date] ? The Filter() expression is testing to see if the IncidentKeys are the same?

    I'm grateful for the solutions, but I always try to make sure I understand what it is, so I can try and do it myself the next time. 

    Thanks, Brent


    Brent


    • Edited by bvanscoy678 Wednesday, October 22, 2014 1:22 AM
    Wednesday, October 22, 2014 1:20 AM
  • RANKX allows you to rank in the context of a particular table.  By then adding the filter you can break that table down into segments, which can either be explicitly called with a static value or by using the EARLIER function (which could just as easily be called CURRENTROW), you can set the filter based on a partially dynamic value being that of the value within the column you have selected within the current row.

    Wednesday, October 22, 2014 10:57 AM
  • RANKX() steps over each of the rows in the table passed to it (argument one), and ranks those rows based on the evaluation of <expression> (argument two).

    Since RANKX() is being used in a calculated column, it is being run independently for each row of the table in that row's context.

    FILTER() is thus also being evaluated on every row, but FILTER() creates its own row context. FILTER() does something similar to RANKX() in that it steps through every row in the table passed to it (argument 1). FILTER()'s second argument is a Boolean expression evaluating to true or false for each row of the table in argument 1; then FILTER() returns a table consisting only of those rows where the second argument evaluated to TRUE.

    As you can see we've repeated the phrase "for each row" in both of those descriptions.

    RANKX() is calculated in the row context of the rows in your table. FILTER() then accepts that entire table as an argument and creates a new row context for its Boolean expression to evaluate in. The test we pass to FILTER() is [IncidentKey] = EARLIER([IncidentKey] (side note, it is considered a best practice to always use fully qualified column names, even where not strictly necessary). The first [IncidentKey] refers to the current row context, which within FILTER() is whatever row the function is currently on in its iteration. EARLIER([IncidentKey]) is referring to our earlier row context, which is from the table where RANKX() is being evaluated.

    Thus, RANKX() has a single [IncidentKey] in context at any time. FILTER() starts with all [IncidentKey]s in context and loops through each row in the fact table, returning only those rows where [IncidentKey] is the same as the row context in the outer table (where it shares row context with RANKX()). So RANKX() is going to iterate over every row in your fact table where [IncidentKey] is the same as the row RANKX() is currently being evaluated on.

    I hope this helps clarify. Nesting row contexts are one of the more difficult aspects of DAX in my experience.

    Wednesday, October 22, 2014 5:03 PM
  • Yes, this helps a lot. I also see by using RANKX and FILTER, I'm taking a lot of power to run on the calculation. The EARLIER makes more sense now that I reviewed my lesson on it (PowerPivot Pro online class). It's like a table within a table.  Your point to well taken about qualify my column names; I'll make a point to clean that up. Because I work strictly with response times (unit arrivals, groups of units, performance measures), its a bit harder to find examples. 

    Thanks to both of you for taking the time to explain; I appreciate it. Both were helpful follow up post. 

    Brent


    Brent

    Wednesday, October 22, 2014 11:02 PM
  • Hi,

    I had a follow up question concerning units that did not arrive (apparatus.arrivaldate). Although the expression does not show the RANK if a unit does not arrive, it is "counting" it as a unit. In my example I have 4 units that do not have an arrival time. They are listed at top and my first unit that has an arrival time starts with the RANK of 5. 

    First, I changed the [order} parameter to 1 (TRUE). I then attempted to wrap the expression with Calculate to filter out any units that did not have an arrival date, but I get an error message: [arrivaldate] column could not be found or could may not be used in this expression.

    =CALCULATE(IF(ISBLANK([ArrivalDate]), BLANK(), RANKX(FILTER(Apparatus, [IncidentKey] = EARLIER([IncidentKey])), [arrivaldate],,1)),apparatus[arrivaldate]=BLANK()) 

    Then I eliminated to IF(ISBLANK...

    =CALCULATE(RANKX(FILTER(Apparatus, [IncidentKey] = EARLIER([IncidentKey])), [arrivaldate],,1),apparatus[arrivaldate]=BLANK())

    My error message says, "Earlier/Earliest refers to an earlier row context which doesn't exist. 

    Any other suggestions are greatly appreciated. 

    Thanks, Brent


    Brent

    Monday, October 27, 2014 1:15 PM
  • My first question is this: why are you including apparatus[arrivaldate] =BLANK() in your CALCULATE() this is saying to only perform the calculation on rows where there is no arrival date.

    I'd try leaving your IF() and your RANKX() alone.

    Try changing your FILTER to this:

    FILTER(Apparatus
        , Apparatus[IncidentKey] = EARLIER( Apparatus[IncidentKey])
            && Apparatus[Response Time] > -1
    )

    So it should be

    RankMeasure:=
    IF(
        ISBLANK(Apparatus[ArrivalDate])
        , BLANK()
        , RANKX(
            FILTER(Apparatus
                , Apparatus[IncidentKey] = EARLIER( Apparatus[IncidentKey] )
                    && Apparatus[Response Time] > -1
            )
            , [ArrivalDate]
        )
    )

    Our IF() already worked appropriately before, so there's no reason to change that. Our RANKX() was also working appropriately, but we were including too many rows to rank over. Thus, the solution lies in restricting what our comparison group is for each invocation of RANKX(), and this set is determined by our FILTER().

    Previously we were ranking against all units sharing an [IncidentKey]. This was inappropriate. We only want to rank against other units that arrived. We've already handled those that didn't arrive in the first result of our IF() statement, so we must exclude them from being handled again in the else result.


    • Edited by greggyb Monday, October 27, 2014 3:25 PM
    Monday, October 27, 2014 3:25 PM
  • Hello,

    My thought was to use calculate's filter to eliminated the blank rows from being evaluated by the RANKX. Instead of filtering outside the correct solution, I see what you are doing with filtering inside the RANKX. That makes perfect sense now. I was on the right track, but I was going about it the wrong way. 


    =IF(
          ISBLANK([ArrivalDate])
          , BLANK()
          , RANKX(
               FILTER(Apparatus,
               [IncidentKey] = EARLIER([IncidentKey])
                && apparatus[Response Time]>-1
             )
             , [ArrivalDate]
             ,,TRUE()
          )
    )

    Thank you. Brent


    Brent


    • Edited by bvanscoy678 Monday, October 27, 2014 3:57 PM
    Monday, October 27, 2014 3:49 PM