Forum Discussion
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
| Project | Stage | Event Date | Status |
| Project A | 1 | 01/09/2018 | C |
| Project A | 2 | 01/11/2018 | C |
| Project A | 3 | 01/05/2019 | U |
| Project A | 4 | 01/08/2019 | U |
| Project A | 5 | 01/01/2020 | U |
| Project A | 6 | 01/05/2020 | U |
| Project B | 1 | 01/01/2019 | C |
| Project B | 2 | 01/02/2019 | U |
| Project B | 3 | 01/08/2019 | U |
| Project B | 4 | 01/01/2020 | U |
| Project B | 5 | 01/02/2020 | U |
| Project B | 6 | 01/07/2020 | U |
| Project C | 1 | 01/10/2018 | C |
| Project C | 2 | 01/12/2018 | C |
| Project C | 3 | 01/01/2019 | C |
| Project C | 4 | 01/02/2019 | U |
| Project C | 5 | 01/05/2019 | U |
| Project C | 6 | 01/07/2019 | U |
| Project | Next Event | Next Stage |
| Project A | 01/05/2019 | 3 |
| Project B | 01/02/2019 | 2 |
| Project C | 01/02/2019 | 4 |
Works fine on my end. Check the file out
3 Replies
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] ) )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'
Works fine on my end. Check the file out