topic Re: Calculated column with MAX value in DAX Commands and Tips

Calculated column with MAX value

kmansweden2017 — Tue, 09 May 2023 18:58:25 GMT

Hi,

I am so lost.
I dont really know how I would search for a simular problem since i dont know how to explain it without a visual aid.

I am trying to get the value from the row with the latest date. (Row.2)
Row.1 is the actuall event date (delivery date),

I need to get the latest Real_TB1 in a calculated column Get Latest Real_TB1 (the last column)

Somehow I guess I need to use use MAX EventDate where VehicleNumber = VehicleNumber and then get Real_TB1 from that.

This table has around 300.000 rows

						Calculated Col
EventDate	Delivery_count	Invoice_count	Order_count	Real_TB1	VehicleNumber	Get Latest Real_TB1
20230324	1	1	1	15958	00086348	17418
20230329	0	0	0	17418	00086348	17418
20230325	0	0	0	17418	00086348	17418

Re: Calculated column with MAX value

Greg_Deckler — Tue, 09 May 2023 19:28:54 GMT

kmansweden2017 Try:

Column = VAR __MaxDate = MAX('Table'[EventDate]) VAR __MaxDateRealTB1 = MAXX(FILTER(ALL('Table'), [EventDate] = __MaxDate), [Real_TB1]) RETURN __MaxDateRealTB1

Watch this video if you need more help, especially the last example, Double Lookup.

Re: Calculated column with MAX value

Anonymous — Tue, 09 May 2023 19:53:05 GMT

Thx for the quick respons!
That didnt really work but somehow I manage to get it to work with this.

I will look at the video tomorrow.

Get Latest Real_TB1 = Var GetDate = CALCULATE( MAX( Fact_KobraRetail[EventDate] ), FILTER ( ALL (Fact_KobraRetail ), Fact_KobraRetail[VehicleNumber] = EARLIER( Fact_KobraRetail[VehicleNumber] ) ) ) Var Result = CALCULATE( MAX( Fact_KobraRetail[Real_TB1] ) , FILTER( ALL ( Fact_KobraRetail ), Fact_KobraRetail[VehicleNumber] = EARLIER( Fact_KobraRetail[VehicleNumber] ) ) , FILTER( ALL ( Fact_KobraRetail ), GetDate = Fact_KobraRetail[EventDate] ) ) RETURN Result

//Kman

Re: Calculated column with MAX value

tamerj1 — Tue, 09 May 2023 20:13:30 GMT

Hi kmansweden2017

I guess a little bit of CALCULATE won't harm Greg_Deckler 😉

Latest Real_TB1 =
MAXX (
TOPN (
1,
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[VehicleNumber] ) ),
'Table'[EventDate]
),
'Table'[Real_TB1]
)

Re: Calculated column with MAX value

Anonymous — Thu, 11 May 2023 14:28:43 GMT

Thx tamerj1, that did produce the same result as my calculation did.

Now I have two ways to do this 🙂