how to remove unnecessary commas while creating concatenatex for two columns

[Anonymous]

Hi ,

I am strugling with a very specific use case where I want to remove unnecessary commas created by concatenatex function in summarize columns while grouping two fileds

raw_ds

feature	attribute	project	issue_id
road	motorwy	abc	123
road	motorwy	pqr
road	motorwy	lmn
river	centerline	xyz	456
river	centerline	efg	789
railway	broadguage	hij	333
railway	broadguage	tti
railway	broadguage	syd
railway	broadguage	plm	346

from below query, I got below result:

group_ds =
SUMMARIZECOLUMNS(
raw_ds[feature],
raw_ds[attribute],

"project_concat",CONCATENATEX(raw_ds,raw_ds[project],","),
"issue_id_concat",CONCATENATEX(raw_ds,raw_ds[issue_id],",")
)

feature	attribute	project_concat	issue_id_concat
road	motorwy	abc,pqr,lmn	123,,
river	centerline	xyz,efg	456,789
railway	broadguage	hij,tti,syd,plm	333,,346

However, I dont want unnecessary commas in "issue_id_concat" filed. Do we have solution in DAX?

[amitchandak · Accepted solution]

@Anonymous , Try like

group_ds =
SUMMARIZECOLUMNS(
raw_ds[feature],
raw_ds[attribute],

"project_concat",CONCATENATEX(filter(raw_ds,raw_ds[project] <> "" && not(isblank())) ,raw_ds[project],","),
"issue_id_concat",CONCATENATEX(filter(raw_ds,raw_ds[issue_id] <> "" && not(isblank())) ,raw_ds[issue_id],",")
)

[Anonymous]

@amitchandak 

It works for me.  I learned the use of filter on aggrgation.  Thanks!