Forum Discussion
Conditional reference to another row in table
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
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
The logic is: if Value is negative, take the Value of another ID in the Group (ideally the max).
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
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?
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