topic Re: Conditional reference to another row in table in DAX Commands and Tips

Conditional reference to another row in table

cpmb — Thu, 10 Oct 2019 20:35:56 GMT

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.

Re: Conditional reference to another row in table

AlB — Mon, 14 Oct 2019 08:46:10 GMT

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

Re: Conditional reference to another row in table

cpmb — Sun, 13 Oct 2019 23:04:11 GMT

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

Re: Conditional reference to another row in table

AlB — Mon, 14 Oct 2019 08:51:30 GMT

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

Re: Conditional reference to another row in table

cpmb — Mon, 14 Oct 2019 19:00:05 GMT

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?

Re: Conditional reference to another row in table

AlB — Mon, 14 Oct 2019 20:22:47 GMT

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