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 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.
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.
