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 have a table of values I want to Rank in order. The column value is a calculated measure. It is not just a column in a table.
When I use this formula I get the following output:
Rank Name = RANKX(ALL(Table[Name]),[ColumnValue],,ASC)
When I use the following formula I get the Rank I am expecting
Rank Name = RANKX(ALLSELECTED(Table[Name]),[ColumnValue],,ASC)
My issue is that I want to be able to apply a filter by Name.
Example if I select "B" it should only show "B" with a rank of 2. However, I cannot get the rank to stay when I use ALLSELECTED which shows the correct ranking order. Not matter how I filter the "Name" category it shows the rank as 1. I assume because there is only 1 Name being shown so in the rank it would be 1 because it isn't being compared to something else.
Question:
How do I get the correct rank order AND have that rank stay when I filter the data. Thank you.
Hi, @cchilton ,
Can't you calculate the rank as Calculated Column?
Something like this:
Rank = RANKX(FILTER(Ranking, Ranking[Name] <= EARLIER(Ranking[Name])), Ranking[Name],,ASC, Dense)
And then it stays every time the same?
Thank you for your response! I tried what you suggested and this didn't work for me. My table has millions of rows and in prod more rows will be added all the time. Thus get an error saying there isn't enough memory so I think it has to be a measure.
I did find this when researching and this almost works.
Rank =
RANKX(
FILTER(
ALL(Table[column]),
NOT(ISBLANK([Measure]))
),
[Measure],,ASC,Dense
)
This is the result. It is missing numbers and row 1 is duplicated. From what I have found the issue has something to do with Blanks? I am very new to DAX and Power Bi so I am not sure. I am closer to an answer though so that makes me hopeful.
@cchilton
Could you please give me sample data, so I can test my suggestions please?
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 |
---|---|
109 | |
100 | |
83 | |
76 | |
65 |
User | Count |
---|---|
120 | |
108 | |
98 | |
83 | |
77 |