cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stblonde
Regular Visitor

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 1Picture 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 2Picture 2

 

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

 

Thanks a lot!

Steve

 

 

1 ACCEPTED SOLUTION

Hi @stblonde,



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

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

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:

 

r1.PNG

 

Here is the sample pbix file for your reference.

 

Regards

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

How can I do this ranking in Query Editor ?

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

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.Smiley Happy

 

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

 

t1.PNG

 

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

c1.PNG

 

Regards

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

 

Error.PNG

 

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:

 

Expected.PNG

 

Regards,

Steve

Hi @stblonde,



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

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

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:

 

r1.PNG

 

Here is the sample pbix file for your reference.

 

Regards

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
Not applicable

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

))

Screenshot_7.pngScreenshot_8.png

Not get Rank correctly 

 

 

Anonymous
Not applicable

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

))

Screenshot_7.pngScreenshot_8.png

Not get Rank correctly 

 

 

Hi @v-ljerr-msft

 

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

 

Image.png

 

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

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. Smiley Happy

 

Regards

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors