Forum Discussion

cpmb's avatar
cpmb
Frequent Visitor
6 years ago
Solved

Conditional reference to another row in table

Could somebody explain why I am getting the numbers below in the Output column? I had expected to get those in the Desired Output column instead. DAX Formulas are below. Date Group ID Value O...
  • AlB's avatar
    AlB
    6 years ago

     

    NewCol =
IF (
    Table1[Value] < 0,
    CALCULATE ( MAX ( Table1[Value] ), ALL ( Table1[ID], Table1[Value] ) ),
    Table1[Value]
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

AlB's avatar
AlB
Community Champion
6 years ago

Hi cpmb 

Can you explain how the correct numbers are calculated, what the logic is?

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

  • cpmb's avatar
    cpmb
    Frequent Visitor
    6 years ago

    The logic is: if Value is negative, take the Value of another ID in the Group (ideally the max).

    • AlB's avatar
      AlB
      Community Champion
      6 years ago

       

      NewCol =
IF (
    Table1[Value] < 0,
    CALCULATE ( MAX ( Table1[Value] ), ALL ( Table1[ID], Table1[Value] ) ),
    Table1[Value]
)

       

      Please mark the question solved when done and consider giving kudos if posts are helpful.

      Cheers  Datanaut

      • cpmb's avatar
        cpmb
        Frequent Visitor
        6 years ago

        Thank you AlB . Works perfectly. Could you give me an explanation as to why the Group doesn't need to be referenced in the NewCol formula?