Forum Discussion

Karl-D's avatar
Karl-D
Helper I
3 years ago
Solved

Build Order Summary using CONCATENATEX?

I have an "Orders" table which shows order lines. 

I do not have an Order Header table.

 

Each [OrderNumber] may have multiple rows in the "Orders" table, representing different items from different departments.

 

"Orders" is related to "Department" on a DepartmentKey with a many to 1 relationship.

 

So I can create a visual that looks as follows.
Orders[OrderNumber] | Department[DepartmentDesc]

00001 | dept1

00001 | dept2

00001 | dept3

00002 | dept2

00002 | dept3

How can I create a measure such that each order only appears once and the results look as follows?

Orders[OrderNumber] | DeptListMeasure

00001 | dept1,dept2,dept3

00002 | dept2,dept3

 

I tried experimenting with CONCATENATEX but if I create the following measure in my Orders table, it shows every possible department after each order instead of only the departments that are part of each order.

DeptListMeasure = CONCATENATEX(RELATEDTABLE('Department'),'Department'[DepartmentDesc],",")

 

 

 

 

 

 

  • Karl-D , if tables are already joined , try like

     

    DeptListMeasure = CONCATENATEX(values('Department'),'Department'[DepartmentDesc],",")

     

    or like

     

    DeptListMeasure = CONCATENATEX( Summarize('Orders','Department'[DepartmentDesc]),[DepartmentDesc] ,",")

     

     

2 Replies

  • Karl-D , if tables are already joined , try like

     

    DeptListMeasure = CONCATENATEX(values('Department'),'Department'[DepartmentDesc],",")

     

    or like

     

    DeptListMeasure = CONCATENATEX( Summarize('Orders','Department'[DepartmentDesc]),[DepartmentDesc] ,",")

     

     

  • amitchandak to the rescue again!  Thanks so much.  The second one with the Summarize is what I needed.

    DeptListMeasure = CONCATENATEX( Summarize('Orders','Department'[DepartmentDesc]),[DepartmentDesc] ,",")