get latest text value by columns

[COU_Alan]

hi all

i'd like to get latest text value by columns

belows are sample datas and result what i want

There are comment based on date, time, part, project name.

i want to get latest comment based on date, time, part, project name.

for example part b has 2 comments in project 1

but 'i solved ptrouble' is latest comment, so  part b's project 1 comment is 'i solved ptrouble'

i tried several times but failed.

i need help

thanks 🙂

[OwenAuger · Accepted solution]

Hi @COU_Alan 

Here's one suggested measure.

This handles multiple comments at the latest date/time by concatenating them, and excludes blank comments, but you can adjust this logic if needed.

PBIX attached.

Latest Comment = 
VAR LatestDateTime =
    CALCULATETABLE (
        TOPN ( 1, SUMMARIZE ( Data, Data[date], Data[time] ), Data[date] + Data[time], DESC ),
        KEEPFILTERS ( NOT ISBLANK ( Data[comment] ) ) -- exclude blank comments
    )
-- Concatenate comments if there are multiple at latest date/time
VAR Comments =
    CALCULATE (
        CONCATENATEX (
            Data,
            Data[comment],
            "|"
        ),
        LatestDateTime
    )
RETURN
    Comments

[COU_Alan]

Hi @OwenAuger 
can you help one more thing??

my final goal is add working hour by day(or week whatever) like upper table in image

but using your dax result is like below
(I'm not saying that the dax you gave me is wrong, just that it's telling me what's going on.)

can you help me one more to get result what i want??

[OwenAuger]

Hi @COU_Alan,

Sure, I can give some suggestions for that. We should set up the model a bit more carefully with Date/Time tables.

Can you show an example of the table containing the numerical values (10, 10, 20, 20 etc)?

I just want to make sure I give a suggestion consistent with how your data is set up.

[COU_Alan]

Hi @OwenAuger 

i attached sample data and screenshot that i want to retrun

working time = raw * 30

for example W01 part a's project1 has 5 rows so 150(Minutes)

[raw data]

[result]

Thank you 🙂

[OwenAuger]

Hi @COU_Alan 

It looks like it's easier to treat Latest Comment as a static column, if that meets your requirements.

Then you can included it as a Row field in a matrix like this:

Latest Comment Column = 
CALCULATE (
    CALCULATE (
        CONCATENATEX ( Data, Data[comment], "|" ),
        TOPN ( 1, SUMMARIZE ( Data, Data[date], Data[time] ), Data[date] + Data[time], DESC )
    ),
    ALLEXCEPT ( Data, Data[part], Data[project_name] )
)

It's much easier to place "supplementary" columns on the left (if they are static) as Row fields. Placing them on the right requires some adjustments to the model.

Would this work for you?