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 

4 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you Kelly for your time