DAX: Ranking Date

[stblonde]

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

[Anonymous]

How can I do this ranking in Query Editor ?

[Anonymous]

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.

[v-ljerr-msft]

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.

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]

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) 

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 · Accepted solution]

Hi @stblonde,

---

---

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

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

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

[Gisselbrecht]

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]

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]

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 

[stblonde]

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]

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. 

Regards