cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to Concate two columns based on unique id and another column

Hi,

I've an input as below and want to concate the two columns measure and description and find the record is completed or not by picking up the earliest date of start and end if the id has more than one measure with same value

 id Measure description date 1 Tea start 16/08/2023 1 par start 15/09/2023 1 par end 13/12/2023 2 par start 14/08/2023 2 par end 12/09/2023 3 par start 12/01/2023 3 par end 04/05/2023 3 Par start 10/08/2023

Expected output

 id Measure description Merged date Completed 1 Tea start Tea:start 16/08/2023 N 1 par start par: start, par: end 15/09/2023 Y 1 par end par: start, par: end 13/12/2023 Y 2 par start par: start, par: end 14/08/2023 Y 2 par end par: start, par: end 12/09/2023 Y 3 Par start Par:start, Par:end 12/01/2023 Y 3 Par end Par:start, Par:end 04/05/2023 Y 3 Par start par: start 10/08/2023 N

1 ACCEPTED SOLUTION
Super User

Hi,

Write these calculated column formulas

``Column = Data[Measure]&":"&Data[description]``
``Column 2 = CALCULATE(CONCATENATEX(Data,Data[Column],","),FILTER(Data,Data[id]=EARLIER(Data[id])&&Data[Measure]=EARLIER(Data[Measure])))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Super User

Hi,

Write these calculated column formulas

``Column = Data[Measure]&":"&Data[description]``
``Column 2 = CALCULATE(CONCATENATEX(Data,Data[Column],","),FILTER(Data,Data[id]=EARLIER(Data[id])&&Data[Measure]=EARLIER(Data[Measure])))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi Ashish,

It's working fine and thanks for the quick reply
Now I've edited the question as I noticed the column is not working for some cases. and we need to use the date column as earlist date to check whether the record is completed or not.

Thank you so much

Super User

You are welcome. I do not understand the new requirement at all.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Sorry to create confusion here. inshort I need one more column that can give me the pair description

ex. id 3 has 4rows but out of those rows only two records are completed with start and end other 2 has just start only not the end so not completed. Exclude the record if pair is not there
Is this doable?

Super User

Try these calculated column formulas

``End date of ID and measure = CALCULATE(min(Data[date]),FILTER(Data,Data[id]=EARLIER(Data[id])&&Data[Measure]=EARLIER(Data[Measure])&&Data[date]>EARLIER(Data[date])&&Data[description]="end"))``
``Start date of ID and measure = CALCULATE(max(Data[date]),FILTER(Data,Data[id]=EARLIER(Data[id])&&Data[Measure]=EARLIER(Data[Measure])&&Data[date]<EARLIER(Data[date])&&Data[End date of ID and measure]=EARLIER(Data[date])&&Data[description]="start"))``
``Completed = if(COALESCE(Data[End date of ID and measure],Data[Start date of ID and measure]),"Y","N")``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi Ashish,

Calculated column formulas are working fine but it looks like in some scenarios it is not working.

Ex. In the below case episode started two times and the episode ended after the 2nd episode started but in the episode end it is still picked the 2nd episode started date not the first.

Expecting something like this

 MRN measure_date reason_for_collection_description Measure End date of ID and measure Start date of ID and measure Completed_SDQ 25719 16/08/2023 0:00 Episode Start Teacher N 25719 15/09/2023 0:00 Episode Start Parent 13/12/2023 0:00 Y 25719 13/12/2023 0:00 Episode End Parent 15/09/2023 0:00 Y 28549 12/01/2023 0:00 Episode Start Parent 10/08/2023 0:00 Y 28549 04/05/2023 0:00 Episode Start Parent N 28549 10/08/2023 0:00 Episode End Parent 12/01/2023 0:00 Y

Thank you so much😊

Super User

What should the answer be for MRN 28549?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Form started on 12/01/2023 completed on 10/08/2023. 2nd form started on 04/05/2023 which is still incomplete

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

No problem, thank you so much for your time

Community Support

Hi @P_P2595 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors