Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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 =
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |