Concatinate all results from a Lookupvalue()

[CameronJohnson]

I have two tables, Tasks and Assignments (examples given below). Each task can have multiple (or no) assignees associated with it.

Tasks

id, title

1, "Do stuff"

2, "Do other stuff"

3, "Do something else"

Assignments

taskID, Name

1, "Bob"

2, "Joe"

2, "Bob"

Basically, I want to be able to add a new column to Tasks that will concatenate together the list on names from the records on the Assignments table who's taskID matches the id of the Task using a comma as the delimiter. I'd like to end up with something like this:

id, title, assignees

1, Do stuff, "Bob"

2, Do other stuff, "Joe, Bob"

3, Do something else

Now, if there weren't multiple results for each one, I would use the following DAX:

Assignee = LOOKUPVALUE(Assignments[Name],Assignments[taskID],Tasks[id])

However, that function doesn't work with multiple results (and I'd still need to have a way to concatinate the resulting list).

Any ideas on how I can achieve this?

[Anonymous]

Hi @CameronJohnson ,

Looks like this post has a solution you ar lookign for for the lookup value error

https://community.powerbi.com/t5/Desktop/LOOKUPVALUE-quot-A-table-of-multiple-values-was-supplied-wh...

Thanks,

Tejaswi

[CameronJohnson · Accepted solution]

It wasn't exactly what I was asking, but that did show how to get the first result, which put me on the right track.

Here's what I did to get it to work:

Assignee = CONCATENATEX(FILTER(ALL(Assignments), Assignments[id] = Tasks[taskID]), Assignments[Name],", ")

[ManSaDax]

Thank you so much for posting the solution!