DAX Latest text value in different rows

DAX DAX Calculations
1

I am connecting to an Azure services DB, Direct Query (can’t import) so I can’t do any modeling in power query.

The table I’m working with has data similar to the toy data I’ve provided in the attached excel where I have 3 columns each of which are mutually exclusive - i.e. for every row there will only ever be a value in one of these columns.
I am trying to figure out how I would write some DAX to retrieve the latest value, where one exists, for each of the rows (and give a blank or default value when there’s none).

Perhaps it’s easier to show a screenshot of the source and desired outcome here.
Source data will look something like:
Capture
and the desired output in Power BI using DAX will be measures in the “Latest…” columns as follows:
image

The Latest Date measure is easy enough to write, but I haven’t been able to figure out how to find the latest value for the text fields given the row value, so that I can drop that measure onto the table visualisation and replicate the Excel result (that I’ve hard coded in the toy file attached).
Any help would be greatly appreciated
Rod

Dummy DAX data.xlsx (12.0 KB)

2

I think this is solved.
After reading this post:

I tried this DAX:

Latest Status =
CALCULATE (
FIRSTNONBLANK ( fSource[Status], 1 ),
FILTER (
fSource,
fSource[Date]
= MAX ( fSource[Date] )
)
)

The bit that’s completely new to me is the expression part of the FIRSTNONBLANK function, i.e. ‘1’.
I don’t understand what’s happening here by simply entering an integer.
The DAX seems to work, but it’s bothering me why placing an integer there instead of any expression works.

3

Hello @rodwhiteley,

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you