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.
- mouzicanat14 years agoRegular Visitor
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"?