locked
To create Rank Column Dynamically in PowerPivot 2013 RRS feed

  • Question

  • HI,

    I've recently started working on Powerpivot 2013. I have one data model in Powerpivot and i need to do some enhancements in that. I'm working on one logic, where i have to create a column which can group the records based on some id and then generate the RANK for each record among that Group. Also the rank generation is dynamic based on the filters applied from Pivot tables and Slicers/filters in excel. Below is the sample data.

    ----------------------------------------------------------
    ID       | ChangeDate          |ChangeMonth | Status
    ----------------------------------------------------------
    123     11/27/2015 1:28:05 PM 2015 11 NA
    123 11/27/2015 1:28:06 PM 2015 11 Open
    123 2/8/2016 4:01:24 PM 2016 02 Not-Open
    123 9/15/2016 8:52:16 AM 2016 09 Open
    123 10/14/2016 1:00:21 PM 2016 10 NA
    123 10/14/2016 1:00:21 PM 2016 10 Open
    123 4/20/2017 12:11:58 PM 2017 04 Open
    123 5/2/2017 11:55:07 AM 2017 05 Not-Open
    456     4/22/2016 2:39:40 PM 2016 04 NA
    456 4/22/2016 2:39:41 PM 2016 04 Open
    456 5/11/2016 7:15:23 AM 2016 05 NA
    456 5/12/2016 2:50:40 PM 2016 05 NA
    456 5/25/2016 2:26:24 PM 2016 05 Not-Open
    456 10/31/2017 10:49:47 PM 2017 10 Open
    789 7/1/2016 11:45:24 AM 2016 07 NA
    789 7/1/2016 11:45:24 AM 2016 07 NA
    789 7/1/2016 12:21:49 PM 2016 07 Not-Open
    789 10/25/2017 2:04:21 PM 2017 10 NA
    789 10/30/2017 4:26:20 PM 2017 10 NA
    789 10/30/2017 4:26:20 PM 2017 10 NA
    789 10/31/2017 2:33:29 PM 2017 10 Open
    789 11/1/2017 12:06:59 PM 2017 11 Open

    I want to generate Rank Column to rank the records among some ID based on "ChangeDate" column. The filter is applied on "ChangeMonth" Column. I need to get the latest record for each ID (should be dynamically change based on filter applied on "change month" column. Then I'll be using the latest value (having rank=1) for each id in a measure to get the count of IDs which are open (as per their latest status). For Ex: After applying the rank (without filtering)

    ----------------------------------------------------------
    ID       | ChangeDate          |ChangeMonth|     Status | Rank
    ----------------------------------------------------------
    123     11/27/2015 1:28:05 PM 2015 11 NA   8
    123 11/27/2015 1:28:06 PM         2015 11 Open   7
    123 2/8/2016 4:01:24 PM         2016 02 Not-Open  6
    123 9/15/2016 8:52:16 AM         2016 09 Open   5
    123 10/14/2016 1:00:21 PM         2016 10 NA   4
    123 10/14/2016 1:00:21 PM         2016 10 Open      3
    123 4/20/2017 12:11:58 PM         2017 04 Open      2
    123 5/2/2017 11:55:07 AM       2017 05 Not-Open  1

    456     4/22/2016 2:39:40 PM        2016 04 NA   6
    456 4/22/2016 2:39:41 PM        2016 04 Open   5
    456 5/11/2016 7:15:23 AM        2016 05 NA   4
    456 5/12/2016 2:50:40 PM        2016 05 NA   3
    456 5/25/2016 2:26:24 PM        2016 05 Not-Open  2
    456 10/31/2017 10:49:47 PM       2017 10 Open   1

    789 7/1/2016 11:45:24 AM 2016 07 NA   8
    789 7/1/2016 11:45:24 AM 2016 07 NA   7
    789 7/1/2016 12:21:49 PM 2016 07 Not-Open  6
    789 10/25/2017 2:04:21 PM 2017 10 NA   5
    789 10/30/2017 4:26:20 PM 2017 10 NA   4
    789 10/30/2017 4:26:20 PM 2017 10 NA    3
    789 10/31/2017 2:33:29 PM 2017 10 Open   2
    789 11/1/2017 12:06:59 PM 2017 11 Open   1

    And if we Filter on Change Month like select all months till Sep-2017, then below should be the Rank values.

    ----------------------------------------------------------
    ID       | ChangeDate          |ChangeMonth|Status | Rank
    ----------------------------------------------------------
    123     11/27/2015 1:28:05 PM 2015 11 NA   8
    123 11/27/2015 1:28:06 PM 2015 11 Open   7
    123 2/8/2016 4:01:24 PM 2016 02 Not-Open  6
    123 9/15/2016 8:52:16 AM 2016 09 Open   5
    123 10/14/2016 1:00:21 PM 2016 10 NA   4
    123 10/14/2016 1:00:21 PM 2016 10 Open      3
    123 4/20/2017 12:11:58 PM 2017 04 Open      2
    123 5/2/2017 11:55:07 AM 2017 05 Not-Open  1

    456     4/22/2016 2:39:40 PM 2016 04 NA   5
    456 4/22/2016 2:39:41 PM 2016 04 Open   4
    456 5/11/2016 7:15:23 AM 2016 05 NA   3
    456 5/12/2016 2:50:40 PM 2016 05 NA   2
    456 5/25/2016 2:26:24 PM 2016 05 Not-Open  1

    789 7/1/2016 11:45:24 AM 2016 07 NA   3
    789 7/1/2016 11:45:24 AM 2016 07 NA   2
    789 7/1/2016 12:21:49 PM 2016 07 Not-Open  1

    So that i can select the record where Rank = 1 and Status = Open

    [No. Of Open IDs]:=COUNT(DISTINCTCOUNT(Table, filter(Staus=Open and Rank = 1)) - DISTINCTCOUNT(Table, filter(Staus=Not-Open and Rank = 1))

    NOTE: This measure will be effected by the Pivot table value and Slicer selection.

    I have searched many sites and tried to use many options but nothing seems to be working. 

    Really appreciate your help on this. Its been many days i'm struggling with this problem.

    Thanks and regards

    Satya

    Monday, January 22, 2018 12:27 PM

Answers

  • Hi Satya,

    Thanks for your question.

    In this scenario, you can create a calculated column called Rank as below:

    =
    RANKX (
        FILTER (
            Table1,
            Table1[ID] = EARLIER ( Table1[ID] )
                && Table1[ChangeMonth] = EARLIER (Table1[ChangeMonth] )
                 && Table1[Status] = EARLIER (Table1[Status] )
        ),
        Table1[ChangeDate],
        ,
        ASC,
        DENSE
    )
    For this issue, you can also refer to a similar thread that using rank below:
    Calculating time between transactions

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, January 23, 2018 2:54 AM

All replies