Conditional reference to another row in table

[cpmb]

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	Output	Desired Output
1/1/2019	A	1	5	5	5
1/1/2019	A	2	10	10	10
1/1/2019	A	3	15	15	15
1/1/2019	B	4	20	20	20
1/1/2019	B	5	25	25	25
1/2/2019	A	1	-10	20	30
1/2/2019	A	2	30	30	30
1/2/2019	B	4	35	35	35
1/2/2019	B	5	-10	35	35
1/2/2019	A	3	-10	20	30

Output = IF(

Table[Value]<0,

CALCULATE([Measure],

FILTER(

SUMMARIZE(Table, Table[Date],Table[Group],Table[ID],Table[Value]),

Table[Date]=EARLIER(Table[Date]) && Table[Group] = EARLIER(Table[Group]) && Table[ID] <> EARLIER(Table[ID])

)), Table[Value])

Measure= CALCULATE(

SUM( Table[Value]),

ALLSELECTED(Table[ID]),

FILTER(Table, Table[Date]=Table[Date] && Table[Group]=Table[Group] && Table[ID] = Table[ID]))

Any explanation would be great, Thanks.

[AlB]

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  

[cpmb]

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

[AlB · Accepted solution]

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  

[cpmb]

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]

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