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
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
- AlexisOlson4 years ago
Super User
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.
- mouzicanat14 years agoRegular Visitor
hahaha that meme entirely applies to my workplace.
- mouzicanat14 years agoRegular Visitor
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?- AlexisOlson4 years ago
Super User
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/352676https://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!