Pulling information into one table with rows that match from another table

[Anonymous]

Hi All,

I'm working on a project where I have two tables that have a common field [Bucket Service]

Table 1: webform

Bucket Service	What service does this relate to?
Benefits	Benefits
Bins, waste and recycling	Bins and waste
Bins, waste and recycling	Bins, waste and recycling
Business	Garage for rent
Business	Rent commercial properties
Children and family services	Children and family services
Children and family services	Social care (Children or Adults)
Children and family services	Speech and language
Council and elections	Covid 19
Council Tax	Council Tax, Business Rates, Benefits

Table 2: tbl_wordcloud

Bucket Service	webform What service does this relate to
Adult social care	#ERROR
Benefits	#ERROR
Bins, waste and recycling	#ERROR
Births, deaths and marriages	#ERROR
Business	#ERROR

Ive used the following DAX formula that comes up as error in the tbl_wordcloud.

webform What service does this relate to =
LOOKUPVALUE (
    webform_feedback[What service does this relate to?],
    webform_feedback[Bucket Service], tbl_wordcloud[Bucket service]
)

But what I am aiming for is something that will concantenate all of the values of [What service does this relate to] into one cell for each service

Bucket Service	What service does this relate to?
Benefits	Benefits
Bins, waste and recycling	Bins and waste, Bins, waste and recycling
Business	Garage for rent,  Rent commercial properties
Children and family services	Children and family services, Social care (Children or Adults), Speech and language

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

You are having 1(tbl_wordcloud) to many(webform) relationship because of which lookup is not working.

You can create a measure like below instead:-

Measure 2 = CONCATENATEX ( VALUES ( webform[What service does this relate to?] ),[What service does this relate to?], "," )

Output:-

Thanks,

Samarth

[Anonymous]

Hi Samath,

Thanks you for youre response. 

I tried your solution, however, its given me all the values from the webform table across all of the rows. Is it possible to only have those related to the BucketService to show in the Testing column. 

[Icey]

Hi @Anonymous ,

This issue is caused by the lack of relationship between your two tables. Here is an example:

No relationship or inactive relationship:

Result:

Please create relationships between the two tables and you will get what you want:

You can create a measure like what @Samarth_18 mentioned, or like below:

webform What service does this relate to =
CONCATENATEX (
    RELATEDTABLE ( webform ),
    [What service does this relate to?],
    ", "
)

This expression can also used as a column.

For more details, please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.