How to show multiple lookup/related values in a single cell of a table

[dvnnnaidu]

Hi,

I have two table as below , first one is Tasks table and second is assignment table. One task can be assigned to multiple people. We want to see data in a table visual where it shows as the requirement table.

Tasks Table

Sr.	Task ID
1	Task 1
2	Task 2
3	Task 3
4	Task 4
5	Task 5

Assignment Table

Task ID	Assigned To
Task 1	E1
Task 1	E2
Task 2	E1
Task 3	E3
Task 3	E1
Task 4	E2
Task 5	E2

Requriement

Task ID	Desc	Assigned To
1	Task 1	E1, E2
2	Task 2	E1
3	Task 3	E1, E3
4	Task 4	E2
5	Task 5	E2

Can you please help on how to achieve the above. 

[v-shex-msft]

Hi @dvnnnaidu,

I also think concatenatex function should suitable for your scenario. You only need to write a measure formula to use the current task to lookup correspond assign records.

Measure =
VAR taskList =
    CALCULATETABLE ( VALUE ( Tasks[Task ID] ), ALLSELECTED ( Tasks ) )
RETURN
    CONCATENATEX (
        FILTER ( ALLSELECTED ( Assignment ), [Task ID] IN taskList ),
        Assignment[Assigned To],
        ","
    )

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@dvnnnaidu , Assuming both tables are joined

Try measure like

concatenatex(Assignment,Assignment [Assigned To],",")

refer: https://www.youtube.com/watch?v=du2HSEzng2E

[dvnnnaidu]

@amitchandak  thank you very much, it worked perfectly