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
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.