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
Anonymous
Not applicable

Temp table with Max Values

Hi

I've a data that consists of entries similar tot he below

Full NameTrainingSummaryTrainingDate
Elizabith SmithNot Started1/1/2021
Smith AustinPartial17/8/2021
Jacob BrownPartial3/1/2021
Joh DoeNot Registered3/4/2021
Joh DoePartial3/6/2021
Elizabith SmithComplete30/1/2021
Jacob BrownComplete5/2/2021
Marlene JacobPartial5/5/2021
Marlene JacobNot Started8/9/2021

 

 

I would like to create a table from this data set that will contain ONLY the latest information for each user (as follows):

 

Full NameTrainingSummaryTrainingDate
Elizabith SmithComplete30/1/2021
Jacob BrownComplete5/2/2021
Joh DoePartial3/6/2021
Marlene JacobNot Started8/9/2021
Smith AustinPartial17/8/2021

 

I will use this table later to report against (e.g. how many has complete training, Partial and not Started). But all should be based on latest status.

 

Thanks in advance

 

1 ACCEPTED SOLUTION

@Anonymous You still need Lookup Min/Max:

Table 2 =
  VAR __Table = 
    ADDCOLUMNS(
      SUMMARIZE('Table',[Full Name],"TrainingDate",MAX([TrainingDate])),
      "TrainingSummary",MAXX(FILTER('Table',[Full Name]=EARLIER([Full Name]) && [TrainingDate]=EARLIER([TrainingDate])),[TrainingSummary])
    )
RETURN
  __Table

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

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@Anonymous You want Lookup Min/Max: (2) Lookup Min/Max - Microsoft Power BI Community


@ 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...
Anonymous
Not applicable

Thanks @Greg_Deckler  for your reply. Maybe I used the wrong word. I don't want to create temp table but calculated table that contains information the way I described so that I can report against it.

 

I went to the link you shared but I thought maybe when I mentioned temp table I confuses the question 

@Anonymous You still need Lookup Min/Max:

Table 2 =
  VAR __Table = 
    ADDCOLUMNS(
      SUMMARIZE('Table',[Full Name],"TrainingDate",MAX([TrainingDate])),
      "TrainingSummary",MAXX(FILTER('Table',[Full Name]=EARLIER([Full Name]) && [TrainingDate]=EARLIER([TrainingDate])),[TrainingSummary])
    )
RETURN
  __Table

@ 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...
Anonymous
Not applicable

@Greg_Deckler works like a Charm 🙂

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.