Concatenate with Filter

[admincaleb007]

Not sure why my query isn't working.

I have a table (products-master) with cols productname, user.email, user.role

ProductName	user.email	user.role
productA	myemail@site.com	manager
productA	myemail2@site.com	manager
productA	myemail3@site.com	user
productB	youremail@site.com	manager
productB	youremail2@site.com	user
productB	youremail3@site.com	manager

I'm trying to concatenate all of the manager emails into one row per distinct(ProductName).  The above table would look like this after I'm done with a quick calculated column:

ProductName	user.email	user.role	Calc.Col
productA	myemail@site.com	manager	myemail@site.com, myemail2@site.com
productB	youremail@site.com	manager	youremail@site.com, youremail3@site.com

I created a second table with only ProductName (products-child), and added only productA, productB, as values (not sure why I did this but after many failed queries I made, this is what I did but still failed.  Not even sure if necessary).  If second table is just pointless then great; I'll keep one table.  This is a very small dataset, and now I'm really curious how to solve this, and why my query isn't working.

colum = CONCATENATEX(

filter(

relatedtable('products-child'),

'products-master'[user.role]="manager"

),'products-master'[user.email],", ")

The above query just brings together ALL emails per PRODUCT and does not seem to be filtering based on the role I'm looking for (manager).

Any help would be great.

[v-eqin-msft · Accepted solution]

Hi @admincaleb007 ,

Based on this——I'm trying to concatenate all of the manager emails into one row per distinct(ProductName).  And if you do not want to create a new table, Please try to create a measure:

Manager = 
var _t=SUMMARIZE(FILTER('Table',[user.role]="manager"),'Table'[ProductName],[user.email])
return CONCATENATEX(FILTER(_t,[ProductName]=MAX('Table'[ProductName])),[user.email]," , ")

Output:

Best regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener]

Hi @admincaleb007 ,

do you mean like this? See attached file.