Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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):
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):
If you can help me with, it would be great.
Thanks a lot!
Steve
Solved! Go to 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
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.
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
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
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
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.
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |