cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Spudder112
Helper I
Helper I

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors