Identify value by min/max value of another column

[jdriscoll]

Hi,

I'm trying to identify a value of one column based on a minimum average value of another column. In the example provided below, I'd like to create measures that narrow down to a scalar of the "Specific Skill/Industry" value associated with the minimum and maximum average "Experience Level" score column. So in this example, I'd like to identify Skill A as the minimum average experience level score with 1.25 and Skill C as the maximum average experience level score with 5.0.

I've already created the minimum/maximum experience level scores themselves using the following measure (minimum score measure provided as example):

Min_Score =
VAR Avg_Table =
SUMMARIZE (
Survey_Table,
Survey_Table[Specific Skill/Industry],
"Avg_Score", AVERAGE ( Survey_Table[Experience Level] )
)
VAR Min_Val =
MINX ( Avg_Table, [Avg_Score] )
RETURN
Min_Val

[amitchandak]

@jdriscoll , Try a measure like

calculate(Min( Survey_Table[Experience Level] ), allexcept(Survey_Table,Survey_Table[Specific Skill/Industry]))

or

calculate(Min( Survey_Table[Experience Level] ), filter(allselected(Survey_Table),Survey_Table[Specific Skill/Industry] =max(Survey_Table[Specific Skill/Industry])))

[jdriscoll]

Hi, I tried both those measures and they return numbers. I'm looking for the value in the specific skill/industry column corresponding with minimum or maximum average experience levels.

[v-janeyg-msft]

Hi, @jdriscoll 

Can you share some sample data(table format instead of pictures) and your desired result? Your measure looks fine, What's the difference between it and the result you want? 

Janey

[jdriscoll]

Hi, picking back up on this topic as I've had some time to work on this again.

To back up a bit and simplify things, I have the following sample table, representing survey results (multiple survey responses per person over time):

First thing I want to do is create a variable table so that it's narrowed down to only the most recent survey results by person so I can do further analysis on it, and the filtered table would look like the following:

What is the best way to achieve this?