Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Using lookupvalue within summarize

I have a set of of data that has a list of projects and the dates at which they hit set milestones - I want to be able to understand which milestone is next for each project. I have generated a sample data set below to illustrate. 

 

I believe the code should be close to below, however to add corresponding stage column I am required to do a lookup from a result generated in the summary, which DAX doesn't allow.

 

Summary= ADDCOLUMNS( 
                SUMMARIZE(Table,'Table'[Project]),
                "Next Milestone Date", CALCULATE(MIN('Table'[Event Date], 'Table'[Status]="U")))

 

Status: C = Complete, U = Uncomplete

 

ProjectStageEvent DateStatus
Project A101/09/2018C
Project A201/11/2018C
Project A301/05/2019U
Project A401/08/2019U
Project A501/01/2020U
Project A601/05/2020U
Project B101/01/2019C
Project B201/02/2019U
Project B301/08/2019U
Project B401/01/2020U
Project B501/02/2020U
Project B601/07/2020U
Project C101/10/2018C
Project C201/12/2018C
Project C301/01/2019C
Project C401/02/2019U
Project C501/05/2019U
Project C601/07/2019U

 

ProjectNext EventNext Stage
Project A01/05/20193
Project B01/02/20192
Project C01/02/20194

 

 

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Works fine on my end. Check the file out 

View solution in original post

3 REPLIES 3
AlB
Community Champion
Community Champion

Hi @Anonymous 

 

Try this:

 

Summary =
ADDCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZE ( 'Table'; 'Table'[Project] );
        "Next Milestone Date"; CALCULATE ( MIN ( 'Table'[Event Date] ); 'Table'[Status] = "U" )
    );
    "Next Stage"; LOOKUPVALUE (
        'Table'[Stage];
        'Table'[Event Date]; [Next Milestone Date];
        'Table'[Project]; [Project]
    )
)
Anonymous
Not applicable

@AlB Thanks for the response.

 

Although I agree/understand the logic. Your solution generates the error, "a table of multiple values was supplier where a single value was expected'

 

 

AlB
Community Champion
Community Champion

Works fine on my end. Check the file out 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.