Forum Discussion
Counting same contact per row when mixed in with multiple contacts
- 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 _3If 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 _3pbix is attached
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?
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-p/352676
https://community.powerbi.com/t5/Desktop/How-to-split-the-the-Column-into-Multiple-rows/m-p/253361
- mouzicanat14 years agoRegular Visitor
I was able to replicate this. Thank you!