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

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?

    • AlB's avatar
      AlB
      Community Champion
      6 years ago

      Sure, cause we want to look at the max by Date and Group, right? Context transition establishes filters on those only since with the ALL( ) we are eliminating the filters on ID and Value.

      If you're not familiar with context transition check this out:

      https://www.sqlbi.com/articles/understanding-context-transition/    

       

       

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

      Cheers  Datanaut