Concatenate values from related table

[huguestremblay]

Hello,

I need to concatenate values from a related table but cannot figure out how to get it done.  I have used a measure with CONCATENATEX when I need to concatenate value from a single table, but it doesn't work here.

My scenario is as follows:

I have a Main table (that contains several more fields than what is shown here) and a Clients table that "harmonises" client names.  The two tables are linked by the Client field. Some records apply to multiple clients and I need a summary table visual that will concatenate the various client names on a single row for each ID.

Any help woul be appreciated 🙂

[smpa01 · Accepted solution]

@huguestremblay  

You can write a measure like this

Measure =
CALCULATE (
    CONCATENATEX (
        RELATEDTABLE ( client ),
        client[Final],
        ",",
        MAX ( main[Client] ), ASC
    ),
    CROSSFILTER ( main[Client], client[Client], BOTH )
)

[huguestremblay]

Hello @smpa01 , for some of my records, I am getting duplicate values in the result (e.g. Client A, Client A, Client B).  Is there a way to eliminate the duplkicates to only get each unique value listed once?

Thanks!

[smpa01 · Accepted solution]

@huguestremblay  okay try this

Measure = 
CALCULATE (
    CONCATENATEX (
        SUMMARIZE(RELATEDTABLE ( client ),client[Client],client[Final]),
        client[Final],
        ",",
        MAX ( main[Client] ), ASC
    ),
    CROSSFILTER ( main[Client], client[Client], BOTH )
)

new pbix  is attached

[smpa01 · Accepted solution]

@huguestremblay  

You can write a measure like this

Measure =
CALCULATE (
    CONCATENATEX (
        RELATEDTABLE ( client ),
        client[Final],
        ",",
        MAX ( main[Client] ), ASC
    ),
    CROSSFILTER ( main[Client], client[Client], BOTH )
)

[huguestremblay]

Thanks again, this works perfectly. 

[huguestremblay]

Awesome, thanks a lot!