DAX Create Column in the table with concatenate of information from other table

[dobregon]

Hi guys!

I'm trying to doing a concatenate in a column table with info (filtered) from other table grouping by an item. I have done something similar creating a measure but only with info from the same table and it is a measure. As an example i have 2 tables.

1. Martkets summary
   

   MarketId	MarketName
   1	Paris
   2	Barcelona
   3	London
   4	Rome

2. Products by market
   

   Productid	Productname	Marketid	CategoryId	CategoryName	BigCategoryid	BigCategoryName
   1	Bike	1	1	Ground	1	Sports
   2	Motorbike	1	1	Ground	1	Sports
   3	Jet Bike	1	2	Water	1	Sports
   4	Chairs	1	3	General	2	Furnitures
   5	Tables	1	3	General	2	Furnitures
   5	Kitchen	1	3	Kitchen	2	Furnitures
   6	Bathroom	1	3	BathRoom	2	Furnitures
   7	Screens	1	4	Office	2	Furnitures
   7	Keyboard	1	4	Office	2	Furnitures
   7	Mouses	1	4	Office	2	Furnitures
   1	Bike	2	1	Ground	1	Sports
   2	Motorbike	2	1	Ground	1	Sports
   4	Chairs	2	3	General	2	Furnitures
   5	Tables	2	3	General	2	Furnitures
   6	Bathroom	2	3	BathRoom	2	Furnitures
   3	Jet Bike	3	2	Water	1	Sports
   7	Screens	3	4	Office	2	Furnitures
   7	Keyboard	3	4	Office	2	Furnitures
   7	Mouses	3	4	Office	2	Furnitures
   1	Bike	4	1	Ground	1	Sports
   2	Motorbike	4	1	Ground	1	Sports
   3	Jet Bike	4	2	Water	1	Sports
   4	Chairs	4	3	General	2	Furnitures
   5	Tables	4	3	General	2	Furnitures

Both are linked by marketid. 

What i want is to create 2 columns in the markettable with the concatenate of information of the 2º table. For example the table Sport, I want to take a concatenate (using "/" between words) info from the 2º table only for the bigcategory sport, and the same for Furtinutures in another column, and i'm looking for this result

MarketId	MarketName	Sports	Furnitures
1	Paris	Ground/Water	General/Kitchen/Bathroom/Office
2	Barcelona	Ground	General/Bathroom
3	London	Water	Office
4	Rome	Ground/Water	General

It could be possible?

Thanks in advance,

Kind regards!!!

[amitchandak]

You can use concatenatex

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax.

If tables are joined. You can create desired output in table. Or You can create new table using summarize

https://docs.microsoft.com/en-us/dax/summarize-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[dobregon · Accepted solution]

yes.. i tried including a summarize and filters and it runs for me. so i think it is the solution

Sports= CONCATENATEX (
    SUMMARIZE (
        FILTER ( Products, Products[MarketId] = Markets[ProjectId] && Products[BigCategoryId] = 1),
        Products[CategoryName] 
    ),
    Products[CategoryName] ,
    "/"
)