Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |