Can I filter for value based on calculated rank?

[yasbos]

Hi. I have a table, vT, that is calculated within the measure as a variable.

I added a column to it ranking it based on values in column Value. 

var vRank=  ADDCOLUMNS( vT , "Rnk" ,  rankx(vT,[Value],,DESC,Dense)  )

I now want to select the value based on the rank of my choosing (in red below). It never works. It always just returns the max value.

evaluate { calculate( maxx( keepfilters( vRank ) , [Value] ) , filter( vRank , [Rnk] = 3 ) ) }

Is there a way to get this to work?

Thanks!

[AnalyticsWizard · Accepted solution]

@yasbos 

In the code snippet you provided, there seems to be a small issue with the context in which you're trying to retrieve the value based on the rank. The `MAXX` function returns the maximum value in the column after evaluating an expression for each row in a table—in this case, the maximum value in the `Value` column of the filtered `vRank` table. However, since you're filtering by rank and then using `MAXX`, if there's only one row in the context (which is with rank 3), it will return the `Value` of that row regardless.

To select a specific value based on the rank, you can use the `FILTER` function to narrow down the table to only the rows with the desired rank and then simply retrieve the `Value` column directly, without using `MAXX`. Here’s how you can modify your DAX query:

EVALUATE {
CALCULATE(
SELECTCOLUMNS(
FILTER(vRank, [Rnk] = 3),
"Value", [Value]
)
)
}

This DAX expression filters `vRank` to only include the rows where `[Rnk] = 3` and then selects the `Value` column from those rows. The `SELECTCOLUMNS` function is used to return a single column table of the `Value` column.

This should give you a table with a single column listing the value(s) for the third rank. If you're expecting a single value, make sure your ranking is done properly, and there are no ties that could result in multiple rows with the same rank. If there could be ties and you want just one value, you could wrap the above inside a `TOPN` function to get the top 1 row after the filter.

Remember that in DAX, context is essential, and the context in which you perform the calculation determines the result. Make sure that your `vRank` variable is calculated in the right context and that the rank is being evaluated as expected.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[yasbos]

Thanks, @AnalyticsWizard !