Forum Discussion

JS's avatar
JS
Helper II
3 years ago
Solved

Concatenatex challenges

Hello!

 

I have a set of small data below:

 

ClientProduct/ServiceCountry
Client AProduct 1Malaysia
Client AProduct 2China
Client AProduct 2Hong Kong
Client AProduct 2India
Client AProduct 2Indonesia
Client AProduct 2Malaysia
Client AProduct 2Philippines
Client AProduct 2Singapore
Client AProduct 2Taiwan
Client AProduct 2Thailand
Client AProduct 2Vietnam
Client AProduct 3Hong Kong
Client AProduct 10China
Client BProduct 11Hong Kong
Client BProduct 1Hong Kong
Client BProduct 4Hong Kong
Client BProduct 4Malaysia
Client BProduct 4Singapore

 

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

ClientProduct & Country
Client AProduct 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

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      JS
      Helper II
      3 years ago

      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's avatar
    amitchandak
    Super User
    3 years ago

    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] , " , ")