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 would like create a calculated column called "Program Name" in the "Global Project" for each GlobalProjectKey.
The relevant value is stored in the table "Custom Fields", column "Program Name".
I would like to select the value with the latest "Date Key" value from the "Project Period" table for each "Globalprojectkey" value.
"Global Project" table:
Globalprojectkey | Program Name |
A | Program A |
B | Program C |
1: N (via GlobalProjectKey)
"Project Period" table:
GlobalProjectKey | Datekey | ProjectPeriodKey |
A | 08/2021 | Key 1 |
A | 07/2021 | Key 2 |
B | 08/2021 | Key 3 |
B | 07/2021 | Key 4 |
B | 06/2021 | Key 5 |
1:N (via ProjectPeriodKey)
"Custom Fields" table:
GlobalProjectKey | ProjectperiodKey | Program Name | Other Column A | Other Column B |
A | Key 1 | Program A | ||
A | Key 1 | Other 0 | ||
A | Key 2 | Other 1 | ||
A | Key 2 | Other 2 | ||
A | Key 2 | Program B | ||
B | Key 3 | Program C |
| |
B | Key 4 | Program D |
| |
B | Key 5 | Program E |
|
I tried to solve this via the LOOKUPVALUE function but wasn't sucessfull, because i was not to able to select the value with the latest datekey per project from the project period table.
I would apprechiate your help to find a solution.
Thank you,
Best regards,
Reinhard
Solved! Go to Solution.
Hi waldnerr,
You can try this new column, it is a little long.
Program Name =
var LastestDate =
CALCULATE(
MAX('Project Period'[Datekey]),
FILTER('Project Period',[GlobalProjectKey]=EARLIER('Global Project'[Globalprojectkey]))
)
var LastestKey =
CALCULATE(
MAX('Project Period'[ProjectPeriodKey]),
FILTER('Project Period',[Globalprojectkey]=EARLIER('Global Project'[Globalprojectkey]) && [Datekey]=LastestDate)
)
RETURN
CALCULATE(
MAX('Custom Fields'[Program Name]),
FILTER('Custom Fields',[Globalprojectkey]=EARLIER('Global Project'[Globalprojectkey]) && [ProjectperiodKey]=LastestKey)
)
Here's the file I made with your data, you can try it.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi waldnerr,
You can try this new column, it is a little long.
Program Name =
var LastestDate =
CALCULATE(
MAX('Project Period'[Datekey]),
FILTER('Project Period',[GlobalProjectKey]=EARLIER('Global Project'[Globalprojectkey]))
)
var LastestKey =
CALCULATE(
MAX('Project Period'[ProjectPeriodKey]),
FILTER('Project Period',[Globalprojectkey]=EARLIER('Global Project'[Globalprojectkey]) && [Datekey]=LastestDate)
)
RETURN
CALCULATE(
MAX('Custom Fields'[Program Name]),
FILTER('Custom Fields',[Globalprojectkey]=EARLIER('Global Project'[Globalprojectkey]) && [ProjectperiodKey]=LastestKey)
)
Here's the file I made with your data, you can try it.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, your proposed solution works perfectly.
Thank you too for your valuable input.
@waldnerr Seems like you want Lookup Min/Max - Microsoft Power BI Community
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 |
---|---|
108 | |
105 | |
87 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
96 | |
80 | |
72 |