Forum Discussion
Preferred column value based on another column + conditional blank
I have a difficult problem (or so I think) The table comes from column A to F (green portion) I am able to get column G based on List.Max({[1],[2],[3],[4]}) in power query but I am not able ...
=Record.FieldOrDefault(_,Text.From([Preferred route]),List.Max(List.Range(Record.ToList(_),1,3)))
Thanks, This works for column I although in truck, it returns a null value. I was able to use DAX to complete column I to what is required.
The next challange is how to get to the values of column H, it needs some logic to determine which column has the highest value and return the column (name: 1,2 ,3 or 4)
M code:
=Record.FieldOrDefault(_,Text.From([Preferred route]),List.Max(List.Range(Record.ToList(_),1,4)))
DAX code:
=VAR _p=Table[1]&"|"&Table[2]&"|"&Table[3]&"|"&Table[4] VAR _r=VALUE(0&PATHITEM(_p,Table[Preferred Route])) RETURN IF(_r,_r,MAXX(ADDCOLUMNS(GENERATESERIES(1,4),"v",VALUE(0&PATHITEM(_p,[Value]))),[v]))
