topic Re: Return values for the last date available in DAX Commands and Tips

Return values for the last date available

Anonymous — Mon, 29 Nov 2021 16:57:53 GMT

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?

Re: Return values for the last date available

smpa01 — Mon, 29 Nov 2021 17:26:40 GMT

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] ) ) ) )

Re: Return values for the last date available

Anonymous — Mon, 29 Nov 2021 17:38:20 GMT

Hi, unfortunately it does not work for meand it even seems to return the first registered values, not the last ones...

Re: Return values for the last date available

smpa01 — Mon, 29 Nov 2021 17:42:30 GMT

Anonymous pbix is attached. Please post your pbix if it does not help. I will take a look.

Re: Return values for the last date available

AlexisOlson — Mon, 29 Nov 2021 17:48:02 GMT

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.

Re: Return values for the last date available

Anonymous — Mon, 29 Nov 2021 18:20:42 GMT

Hi, yours works indeed. I took out the sensitive data out of mine and replaced with dummy data. I tried to keep it as original as possible so some of the column names are different but its the same principle. I am putting in a wetransfer link as I cannot upload files in these posts yet : https://we.tl/t-FevGPdVk3Y

Re: Return values for the last date available

smpa01 — Mon, 29 Nov 2021 18:53:57 GMT

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 !!!

Re: Return values for the last date available

AlexisOlson — Mon, 29 Nov 2021 22:08:09 GMT

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 )

Re: Return values for the last date available

VahidDM — Tue, 30 Nov 2021 01:54:24 GMT

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/