Forum Discussion
Temp table with Max Values
Hi
I've a data that consists of entries similar tot he below
| Full Name | TrainingSummary | TrainingDate |
| Elizabith Smith | Not Started | 1/1/2021 |
| Smith Austin | Partial | 17/8/2021 |
| Jacob Brown | Partial | 3/1/2021 |
| Joh Doe | Not Registered | 3/4/2021 |
| Joh Doe | Partial | 3/6/2021 |
| Elizabith Smith | Complete | 30/1/2021 |
| Jacob Brown | Complete | 5/2/2021 |
| Marlene Jacob | Partial | 5/5/2021 |
| Marlene Jacob | Not Started | 8/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 Name | TrainingSummary | TrainingDate |
| Elizabith Smith | Complete | 30/1/2021 |
| Jacob Brown | Complete | 5/2/2021 |
| Joh Doe | Partial | 3/6/2021 |
| Marlene Jacob | Not Started | 8/9/2021 |
| Smith Austin | Partial | 17/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
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
4 Replies
Anonymous You want Lookup Min/Max: (2) Lookup Min/Max - Microsoft Power BI Community
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