Forum Discussion

mouzicanat1's avatar
mouzicanat1
Regular Visitor
4 years ago
Solved

Counting same contact per row when mixed in with multiple contacts

Hello!

 

I have the following example:

TaskContacts
1John
2John, Andy, Stacy
3

Andy, Stacy

4Stacy, 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!

Need Help
Tips and Tricks
  • smpa01's avatar
    smpa01
    4 years ago

    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

     

     

     

     

     

13 Replies