Forum Discussion
Return values for the last date available
Hi everyone,
I am making a report with the following tables:
ProjectList
| Project ID | Project name | Project Owner |
1 | Project X | John Doe |
| 2 | Project Y | Jane Doe |
And
StatusUpdates
| Project ID | Date | Update |
| 1 | 20/11/2021 | Fixed issue 1 |
| 2 | 22/11/2021 | Fixed issue 2 |
| 1 | 21/11/2021 | Fixed issue 3 |
| 2 | 24/11/2021 | Fixed issue 4 |
"ProjectList" filters "StatusUpdates" based on the column "ProjectID". I essentially imported the first table as a table in my report, and I would like to achieve that by clicking on one of the values in that table, it returns the string value in the column "Update" of the table "StatusUpdates" of the latest date. So essentially, when you would click on project 1 in the table, it would return the value "Fixed issue 3" on a card, which is the third record in the second table.
I already tried with SELECTEDVALUE() but I'm not able to fix the issue. Can someone help me?
HI Anonymous
Download this file: https://gofile.io/d/A1SLih
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
8 Replies
Anonymous you can use a measure like this
Measure = CALCULATE ( MAX ( StatusUpdates[Update] ), FILTER ( VALUES ( StatusUpdates ), StatusUpdates[Date] = CALCULATE ( MAX ( StatusUpdates[Date] ), ALLEXCEPT ( ProjectList, ProjectList[Project ID] ) ) ) )Anonymous pbix is attached. Please post your pbix if it does not help. I will take a look.
This appears to work but I'd recommend defining a variable for MaxDate for several reasons:
- Performance. You don't have to recalculate for each row of the table you're filtering.
- Robustness. Avoid potential trouble arising from a context transition inside FILTER.
- Readability. Broken up a bit, the code is often easier for humans to understand.
Anonymous returned and attached
Measure = VAR _0 = MAX ( 'Project Status'[Created] ) RETURN CALCULATE ( MAX ( 'Project Status'[Status Budget] ), FILTER ( VALUES ( 'Project Status' ), 'Project Status'[Created] = CALCULATE ( _0, ALLEXCEPT ( 'Project Status', 'Project Status'[ProjectId] ) ) ) )AlexisOlson thanks !!!
Not quite what I meant. Once a variable is defined, adjusting its filter context with CALCULATE doesn't do anything, so your DAX is equivalent to
Measure = VAR _0 = MAX ( 'Project Status'[Created] ) RETURN CALCULATE ( MAX ( 'Project Status'[Status Budget] ), FILTER ( VALUES ( 'Project Status' ), 'Project Status'[Created] = _0 ) )I was suggesting something like this:
Measure = VAR ProjectLastCreated = CALCULATE ( MAX ( 'Project Status'[Created] ), ALLEXCEPT ( 'Project Status', 'Project Status'[ProjectId] ) ) RETURN CALCULATE ( MAX ( 'Project Status'[Status Budget] ), 'Project Status'[Created] = ProjectLastCreated )
