Concatenate all related text values

[JoshSHardscape]

Hi all,

I would like to make a column based on 2 tables with a one to many relationship. Essentially I want to concatenate all values from related rows in the many table into the correct row of the one table.

Below is an example of this with Table 1 having a one to many relationship with Table 2. The total price column would be easy to achieve with Calculate(Sum('Table 2'[Price])), it's the Supplier/s column that I'm struggling with

Thanks in advance,

[OwenAuger · Accepted solution]

Hi @JoshSHardscape

You can use CONCATENATEX within CALCULATE to do this.

Something like:

Supplier/s =
CALCULATE (
    CONCATENATEX (
        VALUES ( 'Table 2'[Supplier] ),
        'Table 2'[Supplier],
        " ",
        'Table 2'[Supplier]
    )
)

CALCULATE is required for context transition just the same as with your Total Price calculated column, then CONCATENATEX is used to concatenate the distinct values of 'Table 2'[Supplier].

Regards,

Owen

[JoshSHardscape]

Brilliant, just what I needed, wasn't sure when I tried it myself what expression I could use for the concatenatex function!

Thanks very much

-Josh