Forum Discussion

COU_Alan's avatar
COU_Alan
Frequent Visitor
1 year ago
Solved

get latest text value by columns

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's avatar
    OwenAuger
    1 year ago

    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

     

5 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    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's avatar
      COU_Alan
      Frequent Visitor
      1 year ago

      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's avatar
        OwenAuger
        Super User
        1 year ago

        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.