Forum Discussion

admincaleb007's avatar
admincaleb007
Frequent Visitor
4 years ago
Solved

Concatenate with Filter

Not sure why my query isn't working.

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

ProductNameuser.emailuser.role
productA[email protected]manager
productA[email protected]manager
productA[email protected]user
productB[email protected]manager
productB[email protected]user
productB[email protected]

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:

 

ProductNameuser.emailuser.roleCalc.Col
productA[email protected]manager[email protected], [email protected]
productB[email protected]manager[email protected], [email protected]

 

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.
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.