Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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.
@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] , " , ")