locked
Dax Formula Rank Two Columns RRS feed

  • Question

  • Hello everyone, 

    I have some  trouble with Power BI and I hope you will help me with.
    I have a list of Campaigns from Campaign 1 to 4 with the communication date in front, see below (Table 1):

    Table 1
    Date Campaign ID
    02/12/2016 Campaign 1
    03/12/2016 Campaign 2
    06/12/2016 Campaign 3
    11/12/2016 Campaign 4
    04/12/2016 Campaign 1
    07/12/2016 Campaign 3
    08/12/2016 Campaign 2
    05/12/2016 Campaign 1
    09/12/2016 Campaign 2
    10/12/2016 Campaign 3

    I am trying to ranked in DAX each campaign base on the time period, meaning for every campaign I want to increase the rank number through the period, see below (Expected): 

    Expected
    Date Campaign ID  Rank
    02/12/2016 Campaign 1 1
    03/12/2016 Campaign 2 1
    06/12/2016 Campaign 3 1
    11/12/2016 Campaign 4 1
    04/12/2016 Campaign 1 2
    07/12/2016 Campaign 3 2
    08/12/2016 Campaign 2 2
    05/12/2016 Campaign 1 3
    09/12/2016 Campaign 2 3
    10/12/2016 Campaign 3 3

    If you can help me with, it would be great. 
     
    Thanks a lot!
    Steve

     
    • Edited by stblonde Monday, December 12, 2016 1:57 PM html mistake
    Monday, December 12, 2016 1:53 PM

Answers

  • Hi stblonde,

    After I tested, we are bot able to add the column what you want. Because, the Rank has no regular roles, and we can't group by the Campaign ID, the date is difficult to collate. There must be a compare column based on some category if we want to add a rank column. Here is an thread which display the RANKX function clearly.

    Best Regards,
    Angelia

    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.

    • Marked as answer by stblonde Wednesday, December 14, 2016 4:58 PM
    Tuesday, December 13, 2016 10:31 AM
  • Thx, can see now where the problem lies: You use the formula in a calculated column instead of a measure.

    My formula is for a measure, so you can slice and dice your data and the ranks will be created accordingly to the filtered data.

    But if you want to have it static in a calculated column, the Power-Pivot-version would look like this:

    RANKX(FILTER(Table1, Table1[Campaign ID]=EARLIER(Table1[Campaign ID])),Table1[Date],,1)


    Imke Feldmann TheBIccountant.com

    • Marked as answer by stblonde Tuesday, January 3, 2017 4:18 PM
    Monday, January 2, 2017 6:39 PM
    Answerer

All replies

  • Hi stblonde,

    After I tested, we are bot able to add the column what you want. Because, the Rank has no regular roles, and we can't group by the Campaign ID, the date is difficult to collate. There must be a compare column based on some category if we want to add a rank column. Here is an thread which display the RANKX function clearly.

    Best Regards,
    Angelia

    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.

    • Marked as answer by stblonde Wednesday, December 14, 2016 4:58 PM
    Tuesday, December 13, 2016 10:31 AM
  • I think we actually have a chance here:

    RankWithinCampaign_ID =
    RANKX (
        FILTER (
            ALLEXCEPT ( Table1, Table1[Campaign ID] ),
            NOT ( ISBLANK ( [Measure] ) )
        ),
        [Measure],
        ,
        ASC
    )
    

    With

    Measure = SUM(Table1[Date])

    For the "nested" Rank see here: https://www.powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/

    For the numbering problem with ascending sort-order, see here: https://www.powerpivotpro.com/2015/07/rankx-with-ascending-order-to-show-lowest-quotes-by-vendors/


    Imke Feldmann TheBIccountant.com

    Tuesday, December 13, 2016 12:02 PM
    Answerer
  • Hi Steve,

    didn't you get my formulas working? At least as far as I can see, this is pretty much what you asked for - LinkToFile :


    Imke Feldmann TheBIccountant.com

    Wednesday, December 14, 2016 5:30 PM
    Answerer
  • Hello Imke, 

    First of all, Happy New Year and sorry for the late answer !

    Thank you for the formula, it works pretty well through Power BI interface, but is there any chance to make it work through Excel Power Pivot interface too ? 

    Thank you again for your help Imke,

    Steve

    Monday, January 2, 2017 9:17 AM
  • Hi Steve,

    thanks and happy new year to you as well!

    This should work in Power Pivot as well - just change the "ASC" to 1 (Sort order).


    Imke Feldmann TheBIccountant.com

    Monday, January 2, 2017 10:25 AM
    Answerer
  • Imke,

    Thank you too!

    I actually tested this solution, but I get the following message "A circular dependency was detected".

    Thanks,
    Steve
    Monday, January 2, 2017 4:15 PM
  • Imke, 

    To give you more insight I got this solution from the Power BI community, which completely matched what I am looking for, but on Power BI interface too. 

    Rank =
    VAR d = Table1[Date]
    VAR c = Table1[Campaign ID]
    RETURN
        CALCULATE (
            RANK.EQ ( d, Table1[Date], ASC ),
            FILTER ( ALL ( Table1 ), Table1[Campaign ID] = c )
        )

    How could I adapt this formula for Power Pivot ?

    Thanks, 

    Steve


    • Edited by stblonde Monday, January 2, 2017 4:50 PM Translate
    Monday, January 2, 2017 4:50 PM
  • Thx, can see now where the problem lies: You use the formula in a calculated column instead of a measure.

    My formula is for a measure, so you can slice and dice your data and the ranks will be created accordingly to the filtered data.

    But if you want to have it static in a calculated column, the Power-Pivot-version would look like this:

    RANKX(FILTER(Table1, Table1[Campaign ID]=EARLIER(Table1[Campaign ID])),Table1[Date],,1)


    Imke Feldmann TheBIccountant.com

    • Marked as answer by stblonde Tuesday, January 3, 2017 4:18 PM
    Monday, January 2, 2017 6:39 PM
    Answerer
  • Imke, 

    You make my day ! It finally works :)

    Thanks a lot, I appreciate it ^^

    Steve,

    Tuesday, January 3, 2017 4:18 PM