group by and then concateate comma separated

[Anonymous]

Hi, 

I am new to powerbi and using CRM dynamics as datasource.

I have 4 tables, 1) activitypointers, 2) activityparties 3)incident 4) contacts

My current table visual is like this 

Ticket Number(incident)	Activityid (activitypointer)	partyid(activityparty)	contact
1	a	x	xname
1	a	y	yname
1	a	z	zname
2	b	b	bname
3	a1	x1	x1nam
3	a2	x2	x2name

All I am expecting it to be is

ticketnmber	contact
1	xname, yname, zname
2	bname
3	x1name, x2name

any help is highly appreciated.

Thanks,

Ashay

[v-juanli-msft · Accepted solution]

Hi @Anonymous

TomMartens's suggestion is helpful when the columns his formula used are all in the same table.

But, in your scenario, all these four columns are in four different tables.

It seems"incident" table and "contacts" table are not related directly, (from the Relationship view, there is no direct relationship between two tables), 

If your relationship is like

You could create a column in "contacts" table,

related_ticketnmber = RELATED(incident[Ticket Number])

Then create a measure or use TomMartens's measure

Measure = CONCATENATEX(FILTER(ALL(contacts),contacts[related_ticketnmber]=MAX(contacts[related_ticketnmber])),[contact],",")

If your relationship is not like that so that you can't create a column as above.

Please let me know what exactly relationships there are.

Best regards

Maggie

[Anonymous]

Hi Maggie,

Sorry for late reply. 
Actually, I have fixed this by myself. 
Step 1) I created custom column  = Related(contacts(fullname)) .

step 2 ) Created measure = 

Handoff Name = CONCATENATEX(VALUES(activityparties[PartyName]),activityparties[PartyName],", ")

It shows correct data, but I am getting an extra comma at begining of data. 
, Robert David, David Shaw, Morning Evening, Johnathon Arnold, Leslie Fejes

I tried to use Mid function, but after editing formula, visual keeps loading for some time and then shows error msg saying runs out of memory.. 

Any clue on this?? 

Kind Regards,

Ashay

[TomMartens]

Hey,

this measure:

concatenax contact = 
IF(HASONEVALUE('Table1'[Ticket Number(incident)])
    ,var tickets= VALUES('Table1'[Ticket Number(incident)])
    return
    CONCATENATEX(
        FILTER(
            ALL(Table1)
            ,'Table1'[Ticket Number(incident)] in tickets
        )
        ,'Table1'[contact]
        ,", "
    )
    ,BLANK()
) 

allows this:

Hopefully, this is what you are looking for.

Regards,
Tom