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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.