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.
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
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!
Solved! Go to 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
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
@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...
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)
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 |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |