Counting same contact per row when mixed in with multiple contacts

[mouzicanat1]

Hello!

I have the following example:

Task	Contacts
1	John
2	John, Andy, Stacy
3	Andy, Stacy
4	Stacy, John

I want to pull data that shows me number of tasks each contact was assigned to.
Example: John = 3, Andy = 2, Stacy = 3.

I thought this could easily be combined when filtering, but it still separates into different groups: John | John, Andy, Stacy | Stacy, John.

Any ideas here? Thank you!

[smpa01 · Accepted solution]

@mouzicanat1  for whatever reason you can't do what @AlexisOlson  is suggesting, DAX can still rescue.

You need a slicer table first

Slicer =
VAR _1 =
    ADDCOLUMNS ( tbl, "new", SUBSTITUTE ( tbl[Contacts], ",", "|" ) )
VAR _2 =
    GENERATE (
        _1,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
            "persons", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
        )
    )
RETURN
    SUMMARIZE ( _2, [persons] )

which will give you this

then you can write a measure like this

Measure =
VAR _1 =
    ADDCOLUMNS ( tbl, "new", SUBSTITUTE ( tbl[Contacts], ",", "|" ) )
VAR _2 =
    GENERATE (
        _1,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
            "persons", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
        )
    )
VAR _3 =
    COUNTX (
        FILTER ( _2, [persons] = SELECTEDVALUE ( Slicer[persons] ) ),
        [persons]
    )
RETURN
    _3

If you want the Total to be reconciled too

Measure2 =
VAR _1 =
    ADDCOLUMNS ( tbl, "new", SUBSTITUTE ( tbl[Contacts], ",", "|" ) )
VAR _2 =
    GENERATE (
        _1,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
            "persons2", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
        )
    )
VAR _3 =
    SUMX (
        ADDCOLUMNS (
            Slicer,
            "ct", COUNTX ( FILTER ( _2, [persons2] = EARLIER ( [persons] ) ), [persons2] )
        ),
        [ct]
    )
RETURN
    _3

pbix is attached

[mouzicanat1]

oo! I will try that. Thank you!! 
I was also considering expanding to new rows, but was unable to find that option. I only found forums from years ago, so maybe that feature is gone?

[AlexisOlson]

It's definitely still around. This article explains the steps pretty clearly:
https://exceloffthegrid.com/power-query-split-delimited-cells-into-rows/

Prior community posts:
https://community.powerbi.com/t5/Desktop/Split-comma-delimited-cell-into-multiple-rows-keeping-row/m...

https://community.powerbi.com/t5/Desktop/Splitting-multiple-comma-delimited-cells-into-multiple-rows...

https://community.powerbi.com/t5/Desktop/How-to-split-the-the-Column-into-Multiple-rows/m-p/253361

https://community.powerbi.com/t5/Desktop/Split-column-not-into-seperate-columns-but-in-rows/m-p/1119...

[mouzicanat1]

I was able to replicate this. Thank you!

[AlexisOlson]

Nice. 🙂

This is a fun nut to crack but I'd offer this characterization to anyone thinking about implementing it in any serious work product:

Always nice to have more options and see different solutions though regardless of their practicality.

[mouzicanat1]

hahaha that meme entirely applies to my workplace.

[AlexisOlson]

I'd strongly recommend expanding the table in the query editor to have one row per Contact rather than trying to work with rows containing combined names.

[mouzicanat1]

Thank you for the response!

I figured to do that, but it's a long list of contacts, which also often changes. But I guess I will do that for now.

[AlexisOlson]

Power BI can handle lots of rows much more easily than picking apart fewer rows.

Changing often shouldn't be a problem provided you're splitting the rows automatically in the query editor rather than doing it manually.

[mouzicanat1]

Hello again!

I did just that, but as the data was updating over time. The new rows wouldn't split automatically. I would have to repeat the splitting again.
Is there a feature I'm missing here to always have it auto-split when seeing a row that has a "comma"?

[AlexisOlson]

If the splitting is a step in your query to load the data, I don't know why new rows wouldn't split. Maybe you need to move that step later in your query? Hard to say without seeing the query.

[mouzicanat1]

Hi Alexis,

Really appreciate the quick response!

Looks like I need to re-enter the split. Additionally, I need to re-enter the value replacement steps (some contacts are entered in different formats, so I need to replace the values). Let me know if more information is needed from me.

[AlexisOlson]

It seems odd to me that you need to repeat these steps so many times. Is your table getting wider? If so, I'd strongly recommend unpivoting some of your columns if it makes sense to do so.