Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kmansweden2017
Frequent Visitor

Calculated column with MAX value

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  
EventDateDelivery_countInvoice_countOrder_countReal_TB1VehicleNumberGet Latest Real_TB1  
20230324111159580008634817418  
20230329000174180008634817418  
20230325000174180008634817418  
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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]
)

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

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]
)

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

Now I have two ways to do this 🙂

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors