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
Hello again!
I did just that, but as the data was updating over time. The new rows wouldn't split automatically. I would have to repeat the splitting again.
Is there a feature I'm missing here to always have it auto-split when seeing a row that has a "comma"?
If the splitting is a step in your query to load the data, I don't know why new rows wouldn't split. Maybe you need to move that step later in your query? Hard to say without seeing the query.
- mouzicanat14 years agoRegular Visitor
Hi Alexis,
Really appreciate the quick response!
Looks like I need to re-enter the split. Additionally, I need to re-enter the value replacement steps (some contacts are entered in different formats, so I need to replace the values). Let me know if more information is needed from me.
- AlexisOlson4 years ago
Super User
It seems odd to me that you need to repeat these steps so many times. Is your table getting wider? If so, I'd strongly recommend unpivoting some of your columns if it makes sense to do so.