CONCATENATE different rows

[romovaro]

HI Pbix community and Happy Friday,

I need help with a Formula. I have a customer table and every customer has small entitites based in different countries with different CUID number.

CID	Client Name	Country	CUID
011050	Client A	Germany	025679
011050	Client A	Great Britain	025682
011050	Client A	Hong Kong	025686
011050	Client A	Indonesia	034152
011050	Client A	Indonesia	057145
011050	Client A	India	025687
011050	Client A	India	382279
011051	Client B	Singapore	025723
011052	Client C	United Arab Emirates	025727
011052	Client C	China	025728
011052	Client C	Egypt	025729

I currently show the Count of Countries and Count of CUIDs included in the Client Name but I have been asked to provide in detail de Countries and CUIDS

In that case, add an extra column with "Countries Included" and another one with "CUIDs included"

Example (From the first table),

Client A  should show

Countries included Germany, GB, Hong Kong, Indonesia & India 

Cuids included: 025679, 025682, 025686, 034152, 057145, 025687 & 382279

Client B  should show

Countries included Singapore 

Cuids included: 025723

I currently have a formula that provides the result I need but all the Services are in one row. In the Country and CUID case I have some issues with data in different rows.

Distinct Services =

CONCATENATEX(

FILTER(

SUMMARIZE('Sales File','Sales File'[Client_Name],'Sales File'[ServiceName]),'Sales File'[Client_Name]=EARLIER('Sales File'[Client_Name])

),

'Sales File'[ServiceName],

" & "

)

Thanks for your help,

@jgeddes 

[jgeddes]

Can you show me what you mean by the countries and CUIDs are in different rows?

[romovaro · Accepted solution]

HI jgeddes,

NO worries. Tried again with the formulas and now is working. 

Thanks,

Distinct Countries =

CONCATENATEX(

FILTER(

SUMMARIZE('Weekly Slippage','Weekly Slippage'[Client Name],'Weekly Slippage'[Country]),'Weekly Slippage'[Client Name]=EARLIER('Weekly Slippage'[Client Name])

),

'Weekly Slippage'[Country],

" & "

)

Distinct CUIDs =

CONCATENATEX(

FILTER(

SUMMARIZE('Weekly Slippage','Weekly Slippage'[Client Name],'Weekly Slippage'[CUID]),'Weekly Slippage'[Client Name]=EARLIER('Weekly Slippage'[Client Name])

),

'Weekly Slippage'[CUID],

" & "

)

[jgeddes]

Good Morning,

Using your formula I end up with

I am not sure that I understand where your issue is occuring. Can you elaborate as to what issue(s) you are seeing?

[romovaro]

HI Jgeddes

I was just trying to provide an example of what I am looking for. The last formula is one you gave to me and it works perfectly. The concatenate formula is proving with all the services (all the services are in one row).

My problem now is that CUIDs and Countries are in different rows and the formula is not working.

You can forget about the last part with the "concatenate formula". it was just an example

Thnaks