cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jonesie92 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User
Super User

Re: Using lookupvalue within summarize

Works fine on my end. Check the file out 

3 REPLIES 3
Highlighted
Super User
Super User

Re: Using lookupvalue within summarize

Hi @Jonesie92 

 

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]
    )
)
Jonesie92 Frequent Visitor
Frequent Visitor

Re: Using lookupvalue within summarize

@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'

 

 

Super User
Super User

Re: Using lookupvalue within summarize

Works fine on my end. Check the file out