Help with Finding Max Value in a Row and Tie to Expression

[JohnnyR]

Hello! I have the summary table below where the % is allocation (row) of proficiency (by column). In the last column (First MaxResponseLA), I am trying to call out the highest proficiency by skill (row). 

Using the first row as an example, there is a .5 score for both Established and Developing.  I need logic that gives me the highest allocation, but in this case they are both .5. I would like to take the higher of the two, so the output should be "Established." As you can see, it is giving me "Developing." Looking at row Problem Solving, all allocation went to Developing and Developing is listed under the First Max Response column. I a just not sure my logic is correct using Max since I have more than 2 columns (scaler) to take into account. Any ideas how to get this right? 

MaxResponseLA% =

VAR MaxResponse = max(SkillAverages[LA_Established%], SkillAverages[LA_Developing%])

Return

if(SkillAverages[LA_Developing%] = MaxResponse, "Developing",

if(SkillAverages[LA_Emerging%] = MaxResponse, "Emerging",

if(SkillAverages[LA_Enabling%] = MaxResponse, "Enabling",

if(SkillAverages[LA_Established%] = MaxResponse, "Established",

if(SkillAverages[LA_Influencing%] = MaxResponse, "Influencing")))))

[sroy_16]

Hello,

Please try using this DAX and see if it works.

MaxResponseLA% =
VAR MaxResponse = MAXX(
{
(SkillAverages[LA_Established%], "Established"),
(SkillAverages[LA_Developing%], "Developing"),
(SkillAverages[LA_Emerging%], "Emerging"),
(SkillAverages[LA_Enabling%], "Enabling"),
(SkillAverages[LA_Influencing%], "Influencing")
},
[Value]
)
RETURN
MAXX(
FILTER(
{
(SkillAverages[LA_Established%], "Established"),
(SkillAverages[LA_Developing%], "Developing"),
(SkillAverages[LA_Emerging%], "Emerging"),
(SkillAverages[LA_Enabling%], "Enabling"),
(SkillAverages[LA_Influencing%], "Influencing")
},
[Value] = MaxResponse
),
[Proficiency]
)

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

[JohnnyR]

Hello Sroy, thanks. Sorry, I am new to DAX completely. I sort of follow your message, but where you have "[value]" and "[proficiency]", I am not sure what those are supposed to be. Can you provide more guidance on what my inputs there should be from the data? Thanks.

[v-junyant-msft]

Hi @JohnnyR ,

This is the Power Query forum, and your desired results can be easily achieved with Power Query, but if you want to do it with DAX that would be a very complicated thing to do.
First add a custom column by this M code:

List.Max({[#"LA_lnfluencing%"], [#"LA_Enabling%"], [#"LA_Established%"], [#"LA_Developing%"], [#"LA_Emerging%"]})

Then add a conditional column:

And the final output is as below:

Here is the whole M code in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclYIUdJRMoBhPVMEqRSrE60UoBCMLA/EhnC5IAVfvHpdUOQN4WyQnD9CTs/MHEwZG6OoCFBwxak7UMERu1wsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Skill = _t, #"LA_lnfluencing%" = _t, #"LA_Enabling%" = _t, #"LA_Established%" = _t, #"LA_Developing%" = _t, #"LA_Emerging%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Skill", type text}, {"LA_lnfluencing%", Int64.Type}, {"LA_Enabling%", type number}, {"LA_Established%", type number}, {"LA_Developing%", type number}, {"LA_Emerging%", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MAX", each List.Max({[#"LA_lnfluencing%"], [#"LA_Enabling%"], [#"LA_Established%"], [#"LA_Developing%"], [#"LA_Emerging%"]})),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "MaxResponseLA%", each if [#"LA_Established%"] = [MAX] then "Established" else if [#"LA_Developing%"] = [MAX] then "Developing" else if [#"LA_lnfluencing%"] = [MAX] then "Influencing" else if [#"LA_Enabling%"] = [MAX] then "Enabling" else if [MAX] = 0 then null else "Emerging")
in
    #"Added Conditional Column"

Note that I see you mention "but in this case they are both .5. I would like to take the higher of the two, so the output should be Established." I'm not sure how you are defining the priority of Established and Developing, but you can define your priority by changing the order of the definitions over here.

I.e., if you define Established first and then Developing, it will return Established first if the Value is the same, and the same for the others, so please adjust the order of definitions yourself.

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

[JohnnyR]

Hi Dino, thanks so much for your reply! That solution above is a bit complex for me in DAX. You mentioned it would be easier using power query. I am also not too familiar with that. Can you explain and provide details as in your prior explanation? Maybe the is the direction I should go. Also, to explain, the proficiency score follows rank as below: Influencing = 5, Enabling = 4, Established = 3, Developing =2, and Emerging =1. So, if there were 2 with .5, then the logic should select the proficiency with the highest score. Thanks!

[v-junyant-msft · Accepted solution]

Hi @JohnnyR ,

Sorry for being late! Just returned to work from vacation.

The method I provided above is the Power Query method, not DAX. Power Query is using M code. I'll go over how to do this again in Power Query in as much detail as I can.

After you connect to your datasource, open Power Query from here:

Then it may looks like this:

Click Add column > Custom Column to add a custom column:

And put this M code into this:

List.Max({[#"LA_lnfluencing%"], [#"LA_Enabling%"], [#"LA_Established%"], [#"LA_Developing%"], [#"LA_Emerging%"]})

Then it will look like this in Power Query:

After that, click Add column > Conditional column here to add another column:

Arranged in order from 5 to 1 as you provided above, you should set it up like in the screenshot below:

Then all operations are finished and you can get your final result!

Finally, click Close & Apply to return to Power BI Desktop.

If you want to try to see for yourself what actions I've done, you can create a Blank Query (Home > New Source > Blank Query):

And open Advanced Editor, it may look like this:

Just delete the contents and replace it with the following M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclYIUdJRMoBhPVMEqRSrE60UoBCMLA/EhnC5IAVfvHpdUOQN4WyQnD9CTs/MHEwZG6OoCFBwxak7UMERu1wsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Skill = _t, #"LA_lnfluencing%" = _t, #"LA_Enabling%" = _t, #"LA_Established%" = _t, #"LA_Developing%" = _t, #"LA_Emerging%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Skill", type text}, {"LA_lnfluencing%", Int64.Type}, {"LA_Enabling%", type number}, {"LA_Established%", type number}, {"LA_Developing%", type number}, {"LA_Emerging%", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MAX", each List.Max({[#"LA_lnfluencing%"], [#"LA_Enabling%"], [#"LA_Established%"], [#"LA_Developing%"], [#"LA_Emerging%"]})),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "MaxResponseLA%", each if [#"LA_lnfluencing%"] = [MAX] then "Influencing" else if [#"LA_Enabling%"] = [MAX] then "Enabling" else if [#"LA_Established%"] = [MAX] then "Established" else if [#"LA_Developing%"] = [MAX] then "Developing" else if [#"LA_Emerging%"] = [MAX] then "Emerging" else null)
in
    #"Added Conditional Column"

The pbix file I won't provide, it's the same pbix file as in my first reply.

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