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

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

  • stblonde's avatar
    stblonde
    Regular Visitor
    9 years ago

    Hi v-ljerr-msft, 

     

    Thank you ! It works pretty well on Power BI Desktop.

     

     

    But I have another request how can it work directly within Excel Power Pivot interface ? Because the Var function doesn't seem to work outside of Power BI Desktop interface.

     

    Thank a lot,

    Steve

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

      Hi stblonde,

       

      As I am not very familiar with Excel Power Pivot, I would suggest you also go to the Power Pivot Forum for better assistance on this issue. :smileyhappy:

       

      Regards

  • Gisselbrecht's avatar
    Gisselbrecht
    Regular Visitor
    9 years ago

    Hello,

     

    can you explain, how work your formula plz ? it work well for me but i don't understand how works the VAR value in CALCULATE function.

     

    Thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Rank =
      Var Year = 'NDNQI Clinical Unit'[Year]
      Var Qtr = 'NDNQI Clinical Unit'[Q Number]
      Return
      CALCULATE(
      RANK.EQ(Year, 'NDNQI Clinical Unit'[Year]),
      FILTER(ALL('NDNQI Clinical Unit'),'NDNQI Clinical Unit'[Q Number]=Qtr

      ))

      Not get Rank correctly 

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Rank =
      Var Year = 'NDNQI Clinical Unit'[Year]
      Var Qtr = 'NDNQI Clinical Unit'[Q Number]
      Return
      CALCULATE(
      RANK.EQ(Year, 'NDNQI Clinical Unit'[Year]),
      FILTER(ALL('NDNQI Clinical Unit'),'NDNQI Clinical Unit'[Q Number]=Qtr

      ))

      Not get Rank correctly 

       

       

  • MarkyMark1's avatar
    MarkyMark1
    Advocate I
    2 years ago

    Hi how could I modify this to give a different rank when the dates are tied for the same campaign? I don't have access to the query editor to do a index for each row.