cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Spudder112
Frequent Visitor

Can't Rank my Week Ending Dates with Rankx

The below is the table I am working with, and I want to rank by week ending date.

 

Spudder112_1-1659630248683.png

 

I am trying to formula :

 
Week Rank = RANKX(All('Table 1'),'Table 1'[week ending date],,ASC,Dense)
 
But I get the error:
 
A single value for column 'week ending date' in table 'Table 1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
 
The week ending dates look like the below, so I don't understand why it isn't working. Any help appreciated!
Spudder112_2-1659630494811.png

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Spudder112 , Hope you are creating a column not measure

This should work as column

Week Rank = RANKX(('Table 1'),'Table 1'[week ending date],,ASC,Dense)

 

Also it is always better to have a separate Date/week Table

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Spudder112 , Hope you are creating a column not measure

This should work as column

Week Rank = RANKX(('Table 1'),'Table 1'[week ending date],,ASC,Dense)

 

Also it is always better to have a separate Date/week Table

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Ah, I was using the measure feature instead of column. Thank you! Also for reminding me to make a separate date table.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.