topic Re: Build Order Summary using CONCATENATEX? in DAX Commands and Tips

Build Order Summary using CONCATENATEX?

Karl-D — Wed, 28 Dec 2022 02:44:38 GMT

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],",")

Re: Build Order Summary using CONCATENATEX?

amitchandak — Wed, 28 Dec 2022 02:52:59 GMT

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

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

or like

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

Re: Build Order Summary using CONCATENATEX?

Karl-D — Wed, 28 Dec 2022 03:06:34 GMT

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] ,",")