Hi there, I'm trying to create a measure so I can effectively show the latest "Comment" in the table for the latest date however I don't want to show anything for that "Site" if the "Comment" is "N/A".
| Site | Comment | Date |
| A | All OK | 01/01/2023 |
| A | Ran Late | 02/01/2023 |
| A | N/A | 03/01/2023 |
| A | OK | 04/01/2023 |
| A | OK | 05/01/2023 |
| A | N/A | 06/01/2023 |
| B | N/A | 01/01/2023 |
| B | Ran Late | 02/01/2023 |
| B | N/A | 03/01/2023 |
| B | OK | 04/01/2023 |
| B | All OK | 05/01/2023 |
| B | Late | 06/01/2023 |
My code so far creates a table to filter out any "N/A" values -
Comments =
var _CommentsTable =
SELECTCOLUMNS (
CALCULATETABLE (
factCommentsLog,
FILTER (
factCommentsLog,
factCommentsLog[Comments] <> "N/A"
)
),
"Plant", factCommentsLog[Site],
"Issues", factCommentsLog[Comments],
"Date", factCommentsLog[Date],
"Last Date",MAX(factCommentsLog[Date])
)
RETURN
CONCATENATEX(_CommentsTable,[Last Date]& [Date]& " - " & [Site]&" - "&[Comments],UNICHAR(10))
This code seems to work OK in filtering out any Comments values which are N/A. In my next step I want to filter this new table "_CommentsTable" for the latest date so that I would expect it to return just the row for Site B, "Late", 06/01/2023 as Site A is N/A on this date.
I've tried creating a measure for MAX(factCommentsLog[Date]) and can see that this returns "06/01/2023" however if I then try and filter the _CommentsTable by that it doesn't do it. If I try to filter the _CommentsTable by using [Date] = DATE(2023,1,6) then it works fine so I'm at a loss as to why this is happening.
