RankX - Value cannot be determined Either the column does not exist or there is no current row

[Ruedi007]

I try to use rankx on my dataset that is

State	Inhabitants	City
California	3792621	Los Angeles
Texas	2325502	Housten
Texas	1345047	Dallas
California	1301617	San Diego
California	494665	Fresco
Missouri	314627	St Louis
Missouri	130752	Columbia

according to some tutorials (1, 2) the dax for this is

Measure = rankx(ALLSELECTED(Sheet1), [Inhabitants],,1)

But even tho I use exact the same dax function with the same arguments I get the error:

The value for 'Inhabitants' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

And I have no idea why that is. I changed the summarization setting to "Not summarized" but that did not change anything. In the forum ppl say that this error come from wronlgy mistaken Measure and Column Calculation but in the tutorials they all use Measure. 

Could anyone help me here to get this working?

Best!

[v-diye-msft · Accepted solution]

Hi @Ruedi007 

Use this one:

Measure 3 = rankx(ALLSELECTED(Sheet1),FIRSTNONBLANK(Sheet1[Inhabitants],[Inhabitants]),,ASC)

 

Or this one:

Measure 3 = rankx(ALLSELECTED(Sheet1),CALCULATE(SUM(Sheet1[Inhabitants])))

and have a read of this blog on using RANKX in measures  http://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[amitchandak]

@Ruedi007 , if you trying for a measure then should be like this

Measure = rankx(ALLSELECTED(Sheet1), Sum(Sheet1[Inhabitants]),,asc)

or

Measure = rankx(ALLSELECTED(Sheet1), Sum(Sheet1[Inhabitants]),,asc)

For Rank Refer these links - Also check column and Measure rank differ
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[lbendlin]

make sure that Inhabitants is actually a number and not a text.  Also, it is advisable to use measures instead, such as 

i := sum(sheet1[inhabitants])

Measure: = rankx(ALLSELECTED(Sheet1), i)