Forum Discussion

JS's avatar
JS
Helper II
3 years ago
Solved

Concatenatex challenges

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 Prod...
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
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