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
I'd strongly recommend expanding the table in the query editor to have one row per Contact rather than trying to work with rows containing combined names.
Thank you for the response!
I figured to do that, but it's a long list of contacts, which also often changes. But I guess I will do that for now.
- AlexisOlson4 years ago
Super User
Power BI can handle lots of rows much more easily than picking apart fewer rows.
Changing often shouldn't be a problem provided you're splitting the rows automatically in the query editor rather than doing it manually.
- mouzicanat14 years agoRegular Visitor
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"?- AlexisOlson4 years ago
Super User
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.