Forum Discussion
Help with Finding Max Value in a Row and Tie to Expression
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.
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!
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.