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

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