Forum Discussion

stblonde's avatar
stblonde
Regular Visitor
9 years ago
Solved

DAX: Ranking Date

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 (picture 1):

 

Picture 1

 

 

 

 

 

 

 

 

 

 

 

 

 

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 (picture 2): 

 

 

Picture 2

 

If you can help me with, it would be great. 

 

Thanks a lot!

Steve

 

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi stblonde,

    I tried your first solution, but it showed up an Error (The column "date" cannot be found) :smileysad:

    You shouldn't leave the VAR Function (DAX) in your formula. The formula below is for your reference.:smileyhappy:

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

    And the formula above should also meet your requirements:

     

     

    Here is the sample pbix file for your reference.

     

    Regards

11 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi stblonde,

     

    According to your description, you should be able to use RANK.EQ Function (DAX) to create the "Rank" column in this scenario. See my sample below.:smileyhappy:

     

    I assume you have a table called "Table1" like below.

     

     

    Then you should be able to use the formula below to create a calculate column to calculate the Rank for each Campaign.

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

     

    Regards

    • stblonde's avatar
      stblonde
      Regular Visitor
      9 years ago

      Hi v-ljerr-msft,

       

      First, thank you for your reply. My mistake, it's almost what I am looking for, but the Campaign ID can appear the same day with another one. I tried your first solution, but it showed up an Error (The column "date" cannot be found) :smileysad:

       

       

      The idea is to get the ranking per campaign ID within period. For instance below, the Campaign 1 has been communicated 4 times (2, 4, 5 & 8 of december) so it will follow this sequence, but the Campaign 2 has been communicated to on the second of december as well like underneath:

       

       

      Regards,

      Steve

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi stblonde,

        I tried your first solution, but it showed up an Error (The column "date" cannot be found) :smileysad:

        You shouldn't leave the VAR Function (DAX) in your formula. The formula below is for your reference.:smileyhappy:

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

        And the formula above should also meet your requirements:

         

         

        Here is the sample pbix file for your reference.

         

        Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    How can I do this ranking in Query Editor ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I also would like to know same thing, How to achieve this in query editor. I want to index my records basis the date modified column. if one data point has 3 rows, then basis the date modified i should be able to provide index values 1,2,3

      Any help would be appreciated.