Forum Discussion

vibhoryadav23's avatar
vibhoryadav23
Icon for Helper II rankHelper II
4 years ago
Solved

Create a new table base on the value from multiple tables

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 
ValueIdentifier
XA
YA
ZB
PB
QC
Table T3 
ValueIdentifier
XA
SA
TE
UE
VE
Output (Visualization)  
IdentifierValue T1Value T2
AX, YX, S
BZ, P 
CQ 
E T, U, V

 

Can you help me with this? Thanks in advance!

  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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.