Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
yasbos
Resolver II
Resolver II

Can I filter for value based on calculated rank?

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=  ADDCOLUMNSvT , "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!

1 ACCEPTED SOLUTION
AnalyticsWizard
Solution Supplier
Solution Supplier

@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 👍

View solution in original post

2 REPLIES 2
AnalyticsWizard
Solution Supplier
Solution Supplier

@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 👍

Thanks, @AnalyticsWizard !

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.