topic Re: i want to see the latest comments in powerbi table in DAX Commands and Tips

i want to see the latest comments in powerbi table

Yeswanth — Thu, 11 Apr 2024 06:19:49 GMT

I want to showcase the recent comments in table. initially in every ticket we have multiple updates when customer posted their updates in each row, we have the multiple comments and multiple timestamp's specific to the ID .

I want to showcase the recent comments in table.

Re: i want to see the latest comments in powerbi table

Anonymous — Fri, 12 Apr 2024 01:49:38 GMT

Hi Yeswanth

Here is an example of what I did:

I restored some of your data.

1. Create a measure as follows

Measure = VAR _max = CALCULATE(MAX([Date]), ALLEXCEPT('Table', 'Table'[Customer])) RETURN IF(MAX([Date]) = _max, 1, 0)

2. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: i want to see the latest comments in powerbi table

Yeswanth — Fri, 12 Apr 2024 02:06:21 GMT

Hi thanks for your time but this is not the solution I am looking for

1 ID = MULTIPLE COMMENTS in the same field there is no date in the table in the screenshot having date as a text along with the multiple updates daily so I want to see the latest comments

Re: i want to see the latest comments in powerbi table

Yeswanth — Fri, 12 Apr 2024 02:39:34 GMT

Hi , Thanks for your support, but please dont divide the date and customer name both are in the same field specific to the id , customer name was not constant it will varies as per the daily updates only 2 columns
ID : 1
comments: 24/09 hi rakesh
19/03 welcome rakesh
01/01 bye rakesh

i want to see the lastest commnet as per the date in commnets column

Re: i want to see the latest comments in powerbi table

Yeswanth — Fri, 12 Apr 2024 03:00:08 GMT

hi Anonymous please check the below comments

Re: i want to see the latest comments in powerbi table

Ashish_Mathur — Fri, 12 Apr 2024 03:13:15 GMT

Hi,

I am not sure how much i can help but i would like to try. Share data in a format that can be pasted in an MS Excel file and show the expected result.

Re: i want to see the latest comments in powerbi table

Yeswanth — Fri, 12 Apr 2024 05:27:08 GMT

Hi Ashish_Mathur please find the below table wihich shows actual vs expected result. please provide the desired dax ? or any suggestions thanks
Actual Data

ID	Comments
2287	2024-01-16 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend 2024-01-09 13:51:35 - Rakesh (Additional comments) Many sources of pollution were unregulated parts of industrialization during the 19th and 20th centuries until the emergence of environmental regulation and pollution policy in the later half of the 20th century. 2024-01-03 10:20:26 - Rakesh (Additional comments) Pollution has widespread consequences on human and environmental health, having systematic impact on social and economic systems. 2023-12-19 14:11:44 - Rakesh (Additional comments) Although environmental pollution can be caused by natural events, the word pollution generally implies that the contamin 2023-12-12 12:35:37 - kumar (Additional comments) Pollution is the introduction of contaminants into the natural environment that cause adverse change.[1]
2288	2024-01-13 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend 2024-01-09 13:51:35 - Rakesh (Additional comments) Many sources of pollution were unregulated parts of industrialization during the 19th and 20th centuries until the emergence of environmental regulation and pollution policy in the later half of the 20th century. 2024-01-03 10:20:26 - Rakesh (Additional comments) Pollution has widespread consequences on human and environmental health, having systematic impact on social and economic systems. 2023-12-19 14:11:44 - Rakesh (Additional comments) Although environmental pollution can be caused by natural events, the word pollution generally implies that the contamin 2023-12-12 12:35:37 - kumar (Additional comments) Pollution is the introduction of contaminants into the natural environment that cause adverse change.[1]
2230	2024-01-16 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend 2024-01-09 13:51:35 - Rakesh (Additional comments) Many sources of pollution were unregulated parts of industrialization during the 19th and 20th centuries until the emergence of environmental regulation and pollution policy in the later half of the 20th century. 2024-01-03 10:20:26 - Rakesh (Additional comments) Pollution has widespread consequences on human and environmental health, having systematic impact on social and economic systems. 2023-12-19 14:11:44 - Rakesh (Additional comments) Although environmental pollution can be caused by natural events, the word pollution generally implies that the contamin 2023-12-12 12:35:37 - kumar (Additional comments) Pollution is the introduction of contaminants into the natural environment that cause adverse change.[1]

Expected output:
-------------

ID	Comments
2287	2024-01-16 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend
2288	2024-01-13 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend
2230	2024-01-16 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend

Re: i want to see the latest comments in powerbi table

Yeswanth — Fri, 12 Apr 2024 05:29:34 GMT

Hi Ashish_Mathur i want the only latest comment in comments column specific to the ID instead of past comments

Re: i want to see the latest comments in powerbi table

Ashish_Mathur — Fri, 12 Apr 2024 23:44:03 GMT

When i paste that data in MS Excel, i get each comment in a seperate cell but i think those comments (for a specific ID) are in the same cell (seperated with an Alt + Enter). Please share the download link of the Excel file instead.

Re: i want to see the latest comments in powerbi table

Ashish_Mathur — Sat, 13 Apr 2024 03:40:50 GMT

Hi,

I worked with this sample data

and got this result

with this M code

let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Comment", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Comment"), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Comment", Text.Trim, type text}}), #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each ([Comment] <> "")), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Filtered Rows", "Datetime", each Text.BeforeDelimiter([Comment], "-", {0, RelativePosition.FromEnd}), type text), #"Replaced Value" = Table.ReplaceValue(#"Inserted Text Before Delimiter","",null,Replacer.ReplaceValue,{"Datetime"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Datetime"}), #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Datetime", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.Max(_,"Datetime")}}), #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Comment", "Datetime"}, {"Comment", "Datetime"}) in #"Expanded Count"

Hope this helps.

Re: i want to see the latest comments in powerbi table

Anonymous — Mon, 15 Apr 2024 07:36:24 GMT

Hi Yeswanth

Thanks for the reply from Ashish_Mathur , please allow me to provide another insight:

I used the latest data you gave, which according to my tests requires you to have a corresponding ID for each comment, otherwise the following will happen.

no corresponding ID for each row:

1. Create a calculated column as follows

Date = LEFT([Comments], 19)

2. Create a measure as follows

Measure = VAR _max = CALCULATE(MAX([Date]), ALLEXCEPT('Table', 'Table'[ID])) RETURN IF(MAX([Date]) = _max, 1, 0)

3. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.