Using DAX Concenate

[dgwilson]

Link to example

Here's my source data

NetworkID	Staff	Activity	Hours	Amount
YI12345	Phil	RKL	3	300
YI12345	Jeff	RKL	4	440
YI12345	David	RKL	5	1000
YI12345		KONOTZERO	1740
YI12352	Cindy	RKL	3	300
YI12352	Belinda	RKL	4	440
YI12352	Helga	RKL	5	1000
YI12352		KONOTZERO	1740
YI12388	Cindy	RKL	1	80
YI12388	Belinda	RKL	1	80
YI12388	Helga	RKL	1	80
YI12388	Betty	RKL	1	80
YI12388		KONOTZERO	240

-pasting in here as altered the format of the table slightly. It's OK in the attached / linked zip file.

And this is what I want the output to look like

Network ID	Staff	Hours	Dollars
YI12345	Phil, Jeff, David	12	1740
YI12352	Cindy, Belinda, Helga	12	1740
YI12388	Cindy, Belinda, Helga, Betty	4	240

And I currently have this DAX (as a new column) that isn't doing the job fo rme.

I've had a go at using CONCENATEX... and I can't get it to work.

__Network_ID_Employee_CAT =

VAR networkid = FIRSTNONBLANK(Data[NetworkID],1)

VAR employeesTable = CALCULATETABLE(FIRSTNONBLANK(Data[Staff], 1), FILTER(ALL(Data), AND(Data[NetworkID] = networkid, Data[Activity] = "RKL")))

VAR employeesCAT =

IF(networkid <> BLANK(),

CONCATENATEX(employeesTable, employeesTable, ", "),

"")

RETURN

employeesCAT

[JarroVGIT]

Alright there are two approaches here. You either create a calculated table that has the format of your result, or you create measure and create a table visual with your result.

Table visual with your result

I added two measures to the table. Note that we want to concatenate only non-blank values and the same goes for your 'Amount'  column in the desired result: we only want to sum Amount where Staff is not empty.

The first measure shows the concatenated names:

ConcatenatedNames = IF(HASONEVALUE(Table2[NetworkID]), CONCATENATEX(FILTER(Table2, Table2[Staff] <> ""), Table2[Staff], ", "))

Second measure is the sum of amount (where staff is not empty):

AmountMeasure = SUMX(FILTER(Table2, Table2[Staff] <> ""), Table2[Amount])

Then I created a table visual, with the NetworkID, the measures and hours column. This is your end result:

Calculated Table with your result

I created some DAX to give you a table with the desired result. The DAX is as follows:

TableConcat = 
VAR startTable = DISTINCT(Table2[NetworkID])
RETURN
ADDCOLUMNS(startTable, 
"Names", 
    VAR ntw = [NetworkID]
    RETURN
        CONCATENATEX(FILTER(ALL(Table2), Table2[Staff] <> "" && Table2[NetworkID] = ntw), Table2[Staff], ","),
        "Hours", 
            VAR ntw = [NetworkID]
            RETURN
            SUMX(FILTER(ALL(Table2), Table2[Staff] <> "" && Table2[NetworkID] = ntw), Table2[Hours]),
            "Amount", 
                VAR ntw = [NetworkID]
                RETURN
                SUMX(FILTER(ALL(Table2), Table2[Staff] <> "" && Table2[NetworkID] = ntw), Table2[Amount]))

The weird thing is that I had to declare the value of the base column in a variable for every column. I don't know why that is, but the above works and results in this table (from data view):

Hope this helps!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[dgwilson · Accepted solution]

Thank you. Your examples are awesome.

I've been able to build the following (based on your examples) and this is working.

__Network_ID_Employee_CAT =

VAR networkid = FIRSTNONBLANK(Data[NetworkID],1)

VAR employeesTable = CALCULATETABLE(SUMMARIZE(Data, Data[Staff]), FILTER(ALL(Data), AND(Data[NetworkID] = networkid, Data[Activity] = "RKL")))

VAR employeesCAT =

IF(networkid <> BLANK(),

CONCATENATEX(employeesTable, [Staff], ", "),

"")

RETURN

employeesCAT