DAX Measure to Concatenate Unique Strings

[carrl284]

Hi,

I have a table where people have worked dates on a job.  The start and end columns are split to a row for each day. This results in the comments being duplicated for every day. Can anyone show me how to create a measure that will concatenate the comments for each person but not duplicate them?  I tried concatenatex but got "covering for X, covering for X, covering for X, back problems, back problems, back problems" for EmployeeId1.

EmployeeId	Start	End	Days	Category	Comment
1	01 April 2021	03 April 2021	01 April 2021	Covering	covering for X
1	01 April 2021	03 April 2021	02 April 2021	Covering	covering for X
1	01 April 2021	03 April 2021	03 April 2021	Covering	covering for X
2	01 April 2021	02 April 2021	01 April 2021	Overtime	rate * 1.5
2	01 April 2021	02 April 2021	02 April 2021	Overtime	rate * 1.5
1	04 April 2021	07 April 2021	04 April 2021	Sick	back problems
1	04 April 2021	07 April 2021	05 April 2021	Sick	back problems
1	04 April 2021	07 April 2021	06 April 2021	Sick	back problems
1	04 April 2021	07 April 2021	07 April 2021	Sick	back problems
2	24 April 2021	26 April 2021	24 April 2021	Holiday	paid holiday
2	24 April 2021	26 April 2021	25 April 2021	Holiday	paid holiday
2	24 April 2021	26 April 2021	26 April 2021	Holiday	paid holiday

This is how I want to present the data, I have created a measure counting how many days for each category but I cannot work out how to get the comments appearing in one line.

EmployeeId	Start	End	Covering	Overtime	Sick	Holiday	Comment
1	01 April 2021	30 April 2021	3		4		covering for X, back problems
2	01 April 2021	30 April 2021		2		3	rate * 1.5 , paid holiday

Many Thanks

[Anonymous]

@carrl284 can you try pivoting your date columns and your employeeid should be the column name and you should return your comments while pivoting, that way we get the comments on the corresponding days in a row for an employee, you will eliminate the duplicates which you have shown in your image. Then you can try your concatenate DAX, you might get blanks using this method that you can take care of I believe.

Please mark this as solution, if I helped you well.

Thanks

[carrl284]

Hi @Anonymous Thanks for the quick response.  In my actual model there is a lot more going on so I don't think I could face reshaping the data model now.  

[Jihwan_Kim · Accepted solution]

Hi, @carrl284 

Please try the link down below.

Comments =

IF (

ISFILTERED ( 'Table'[EmployeeId] ),

CONCATENATEX (

SUMMARIZE (

FILTER ( ALL ( 'Table' ), 'Table'[EmployeeId] = MAX ( 'Table'[EmployeeId] ) ),

'Table'[Comment]

),

'Table'[Comment],

", "

)

)

https://www.dropbox.com/s/fd5qiaml3f1hwnl/carrl.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[carrl284]

Hi @Jihwan_Kim This is exactly what I needed.  Thank you so much for your quick response.

[Anonymous]

Having a doubt, what if instead of 2 rows for employee id 1, I want 1 row which includes dates starting from 1/4/21 - 30/4/21. How should my DAX be modified? Just Curious!