cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YJAMOUS
Helper IV
Helper IV

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

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

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

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 

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

@Greg_Deckler works like a Charm 🙂

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors