Measure Needed for Best Available Option From Ranked List

[jmiller21]

I need to create a measure that returns the best available option from a ranked list, realtive to two specific fields:  Position and Round.  I have a table that contains the following columns:

• Round (1-20)
• Position (A, B, D, C)
• Rank (1-240)
• Name

Each round has 12 selections.  Also, each round can have anywhere from 0-12 ranked options for each Position A-D; in other words, some Positions may or may not have a ranked option for each Round.

I created a measure already using the MIN function, filtered for Position A, which when placed into a table using Round and the created measure, it only returns a value starting in Round 3, as the first ranked option for Position A did not occur within the first 2 Rounds (24 selections).  What I would like is for the table to return the best available option at any point in the selection process regardless of the first ranking occurance - the result returned in Round 3 should also be shown for Round 1 and 2, as that ranked option was available at those points as well.

What function(s) do I need to make sure the best available option is returned at any point within the 20 round process?

[v-henryk-mstf]

Hi @jmiller21 ,

I did a test based on the data you provided. The reason why position A is not ranked until the third round is because there is only one ranking result for position A3 in the original data source, so the values corresponding to other positions in the matrix are empty.

If there is still confusion, can you further describe your exact needs so that I can do further testing. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmiller21]

Henry - appreciate the feedback.  Yes, what you described is the result I am getting as well, and the reason makes sense.  However, the desired result is that the table would show the best available position rank still available at each round. 

So in rounds 1 & 2, A-1 is certainly available, even though it is not shown as selected/ranked until round 3.  Was hoping there was a measure that would return this result - and that it would be dynamic based on the selection # (as a slicer).  For example, if someone had selection # 4, based on the sample data, the best available would be:

A-1

B-4

C-1

D-1

Maybe it is not a measure that will do this, and I have to create some calculated columns instead?  

[v-henryk-mstf]

Hi @jmiller21 ,

This is not a question of calculated columns or measures. You can use the if condition to judge, when the value in the matrix is empty, fill in the blank value with the ranking result below.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmiller21]

This sounds reasonable. Can you please provide an example of how this measure would look using an if condition to provide the desired result?

[daxer-almighty]

I need text data to work with. Sorry but I don't have time to copy by hand the data from the pictures.

[jmiller21]

Sorry - here is a link to the sheet in Google Drive:

https://drive.google.com/file/d/1yu5t3f-0S7Sh1WreQkFejOR1SVrILFJj/view?usp=sharing

Jacen

[daxer-almighty]

"A picture WOULD BE worth a thousand words." And some data that we could copy and play with would be handy too... If in doubt, please check this: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523

Cheers.

[jmiller21]

Apologize - I was not sure if my example was simple enough to work through, but see below.  Table on the left is an example of how the data would look (limited, only first 32 rows of 240).  I have a related table which has a relationship between "OVER PICK" to the data table's "RANK".  From there, I crated a slicer for "SELECTION" which is 1-12.  Desired result is to slice on Selection, and have the matrix/table return the best available option by Position for each Round.  The example below you will see the data, and how Position A does not get ranked until Round 3.  Most of the measures I have created do not do a good job of translating future availability/selections as available in current rounds, especially in the case of Position A which is not ranked until Round 3.  Hopefully this helps, but if not, please let me know.