Forum Discussion
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 | 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.
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
5 Replies
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
