topic Re: get latest text value by columns in DAX Commands and Tips

get latest text value by columns

COU_Alan — Fri, 21 Feb 2025 01:02:41 GMT

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 🙂

Re: get latest text value by columns

OwenAuger — Fri, 21 Feb 2025 01:48:25 GMT

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

Re: get latest text value by columns

COU_Alan — Fri, 21 Feb 2025 04:43:10 GMT

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

Re: get latest text value by columns

OwenAuger — Sun, 23 Feb 2025 11:00:14 GMT

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.

Re: get latest text value by columns

COU_Alan — Sun, 23 Feb 2025 14:14:54 GMT

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 🙂

Re: get latest text value by columns

OwenAuger — Mon, 24 Feb 2025 22:01:25 GMT

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?