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...
  • 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

     

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper 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.

COU_Alan's avatar
COU_Alan
Frequent Visitor
1 year ago

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

    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?