Take the column with maximum value based on another value

[Anonymous]

I have a table like this:

first	ans1	second	ans2	third	ans3	max
150	yes	170	yes	190	no	170
190	yes					190
110	no					0
130	no	110	yes			110
130	yes	190	no	170	yes	170

It's actually a Gabor-Granger analysis survey, stored like this, queue of columns, price, yes/no, second price, yes/no etc. I need to get max column, strugling couple days already, maybe someone could help? Max column take maximum price with according column ans with yes. The price range is 110-130-150-170-190

[AlexisOlson]

Here's another Power Query answer:

List.Max(
    List.Transform(
        {
            { [first],  [ans1] },
            { [second], [ans2] },
            { [third],  [ans3] }
        },
        each if List.Last(_) = "yes"
             then List.First(_) else null // or 0 if you prefer
    )
)

You can do nearly the same thing in DAX:

MAXX (
    {
        ( [first],  [ans1] ),
        ( [second], [ans2] ),
        ( [third],  [ans3] )
    },
    IF ( [Value2] = "yes", [Value1], 0 )
)

[amitchandak · Accepted solution]

@Anonymous , A solution in Power Query, New custom column

let 
_a = Table.Max(Table.SelectRows( Table.FromColumns({
   {[first],[second],[third]},
   {[ans1],[ans2],[ans3]}
}, {"Col1", "Col2"}), each [Col2] = "yes"), "Col1"),
_b= try Record.Field(_a, "Col1") otherwise 0
in 
_b

Dax New Column

Column = Maxx(filter(union(row("Col1",[first],"Col2", [ans1]), row("Col1",[second],"Col2", [ans2]),row("Col1",[third],"Col2", [ans3])),[Col2]= "yes"),[Col1])+0