March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
Solved! Go to Solution.
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]
)
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]
)
Thx tamerj1, that did produce the same result as my calculation did.
Now I have two ways to do this 🙂
@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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
25 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
39 | |
29 | |
27 | |
20 | |
18 |