Create a new table base on the value from multiple tables

[vibhoryadav23]

Hi,

I have multiple tables which connect to one table through an identifier. I want to summarize the values in a single view like in example below (can be a visualization or a new table). Its almost like a summarization on text concat (something which you can see in other tools like Alterxy but is not offered by Power BI).

Table T1

Identifier

A

B

C

D

E

F

Table T2
Value	Identifier
X	A
Y	A
Z	B
P	B
Q	C

Table T3
Value	Identifier
X	A
S	A
T	E
U	E
V	E

Output (Visualization)
Identifier	Value T1	Value T2
A	X, Y	X, S
B	Z, P
C	Q
E		T, U, V

Can you help me with this? Thanks in advance!

[v-zhangti · Accepted solution]

Hi, @vibhoryadav23 

Measure:

Value T1 = CONCATENATEX('Table 2', [Value], ",")

Value T2 = CONCATENATEX('Table 3', [Value], ",")

Table:

Table =
SUMMARIZE (
    'Table 1',
    'Table 1'[Identifier],
    "Value T1", CONCATENATEX ( 'Table 2', [Value], "," ),
    "Value T2", CONCATENATEX ( 'Table 3', [Value], "," )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[HotChilli]

If you use T1 as the dimension table and relate it 1:m to T2 and T3, you can create 2 measures:

MeasureT3 = CONCATENATEX(TableT3, [Value], ",")

MeasureT2= CONCATENATEX(TableT2, [Value], ",").

Put those in a table visual with identifier from T1