Concatenatex challenges

[JS]

Hello!

I have a set of small data below:

Client	Product/Service	Country
Client A	Product 1	Malaysia
Client A	Product 2	China
Client A	Product 2	Hong Kong
Client A	Product 2	India
Client A	Product 2	Indonesia
Client A	Product 2	Malaysia
Client A	Product 2	Philippines
Client A	Product 2	Singapore
Client A	Product 2	Taiwan
Client A	Product 2	Thailand
Client A	Product 2	Vietnam
Client A	Product 3	Hong Kong
Client A	Product 10	China
Client B	Product 11	Hong Kong
Client B	Product 1	Hong Kong
Client B	Product 4	Hong Kong
Client B	Product 4	Malaysia
Client B	Product 4	Singapore

and i would like to have a desired output like this:

Client	Product & Country
Client A	Product 1 (Malaysia), Product 2 (China, Hong Kong, Malaysia, India, Indonesia, Malaysia, Philippines, Singapore, Taiwan, Thailand, Vietnam), Product 3 (Hong Kong)
Client B	Product 11 (Hong Kong), Product 1 (Hong Kong), Product 4 (Hong Kong, Malaysia, Singapore)

I am able to use concatenatex and values to obtain unique list of product or country values. however I am not able to get around my head in summarizing it in the desired output. 

What would be the DAX approach to have this output. Appreciate the guidance and help! 

Cheers

JS

[johnt75 · Accepted solution]

You can create a calculated column like

Product & country = 
	VAR  Products = CALCULATETABLE(
		VALUES( 'Table'[Product/Service] ), ALLEXCEPT( 'Table', 'Table'[Client] )
		
	)
	VAR  Result = CONCATENATEX( Products,
		VAR  CurrentProduct = 'Table'[Product/Service]
		VAR  Countries = CALCULATETABLE( VALUES( 'Table'[Country] ), ALLEXCEPT( 'Table', 'Table'[Product/Service], 'Table'[Client] ) )
		VAR  CountryString = CONCATENATEX( Countries, 'Table'[Country], ", " )
		RETURN  CurrentProduct & " ( " & CountryString & " ) ",
		", "
	)
	RETURN  Result

[JS]

Hello @johnt75 

Your DAX works seamlessly. Thank you for the pompt and clear DAX. I am able to understand your DAX and the rationale! Thank you again. 

[amitchandak]

@JS , A new measure

Meausre =

Var _tab = client(Table, Table[Client], Table[Product/Service], "_1", concatenatex(Table, Table[Country]) , " , ")

return

concatenatex(_tab , _tab [Product/Service] & " - " & _tab [_1] , " , ")