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
SamWhitehouse
Frequent Visitor

Return Earliest Date

Hi All, 

 

 

I have 2 tables. a Projects table showing a summary of the project and a task table showing a list of all tasks on for each project. 

 

Project NameProject ManagerProject Start DateProject Finish Date
Project 1PM01/01/202001/02/2021
Project 2PM201/02/202101/01/2021
Project 3PM301/03/2020101/04/2011

 

Project NameTaskMilestone
Project 1Task 101/01/2020
Project 1Task 201/02/2021
Project 2Task 101/03/2020
Project  2Task 201/04/2020
Project 3 Task 101/01/2020
Project 3Task 101/01/2020

 

 

I need to be able to pull the next project milestone and name for each project.

 

I have tried using Lookup, however, multiple rows are returned as there may be multiple milestones on a day. 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi

Can you try to add a calculated column on the first table with the following code:

Add a calculated column on table2
Latest Milestone =
VAR Current_Project = Table2[Project Name]
return CALCULATE(MAX(Table1[Milestone]), FILTER(Table1, Table1[Project Name] = Current_Project))

table2

Project NameProject ManagerProject Start DateProject Finish DateLatest Milestone

table1

Project NameTaskMilestone

 

hope this helps

It came from the following post:

https://community.powerbi.com/t5/Desktop/DAX-LOOKUPVALUE-with-MAX/td-p/696076

 

Thank you

Tomas

 

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Hi

Can you try to add a calculated column on the first table with the following code:

Add a calculated column on table2
Latest Milestone =
VAR Current_Project = Table2[Project Name]
return CALCULATE(MAX(Table1[Milestone]), FILTER(Table1, Table1[Project Name] = Current_Project))

table2

Project NameProject ManagerProject Start DateProject Finish DateLatest Milestone

table1

Project NameTaskMilestone

 

hope this helps

It came from the following post:

https://community.powerbi.com/t5/Desktop/DAX-LOOKUPVALUE-with-MAX/td-p/696076

 

Thank you

Tomas

 

 

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.

Top Solution Authors