Forum Discussion

PatrickWong's avatar
PatrickWong
Icon for Helper I rankHelper I
3 years ago

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 ...
Need Help
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

=Record.FieldOrDefault(_,Text.From([Preferred route]),List.Max(List.Range(Record.ToList(_),1,3)))

  • PatrickWong's avatar
    PatrickWong
    Icon for Helper I rankHelper I
    3 years ago

    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)

    • wdx223_Daniel's avatar
      wdx223_Daniel
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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]))