Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Concatenate Dates based on ID

Hi,   Could someone help me with below requirement. I want to concatenate dates against ID as below   Thank in advance Suren
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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's avatar
    Anonymous
    5 years ago

    Thank you Kelly for your time 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Try a new column like

 

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!