Concatenate Dates based on ID

[Anonymous]

Hi,

Could someone help me with below requirement. I want to concatenate dates against ID as below

Thank in advance

Suren

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

First go to query editor>create an index column;

Then create a column as below:

contatenated date = 
var _minindex=CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])))
Return
IF('Table'[Index]<>_minindex,CONCATENATEX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Date],","))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous · Accepted solution]

Thank you Kelly for your time 

[amitchandak]

@Anonymous , Try a new column like

calculate(concatenatex(distinct(Table[Date]), [Date]), filter(Table, [ID] =earlier([ID])))

[Anonymous]

Thank you Amit for you suggestion.

Column = CALCULATE(CONCATENATEX(DISTINCT(Sheet2[Date]),Sheet2[Date]),FILTER(Sheet2,[ID]=EARLIER([ID])))

But the expression didn't help me to get desired output. I need to mimic column "Concatenated Date".

a. if "Date" appears for first time against ID,concatenation should not be done 

 b. if we have different "Date" against same ID then concatenated two dates (First & Second) and sould be shown in row of second occurence 

Thank you in advance

Suren

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

First go to query editor>create an index column;

Then create a column as below:

contatenated date = 
var _minindex=CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])))
Return
IF('Table'[Index]<>_minindex,CONCATENATEX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Date],","))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!