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: 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 cont...
  • smpa01's avatar
    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