Forum Discussion
WorkHard
Helper V
Helper VCount value while excluding text that contains a keyword
I'd like to count distinct results but exclude from this count entries that contain the keyword "This".
Sample Data:
| ID | Display As |
| 1 | This value |
| 2 | Other value |
| 3 | This text |
| 4 | Other text |
| 5 | Other value |
| 6 | Other value |
| 7 | Other value |
The result should be 5.
Here's what I've tried:
CountResult =
CALCULATE (
DISTINCTCOUNT(My Data[ID]),
NOT SEARCH("This",My Data[Display As])
)
Hi WorkHard
I would create a column as
NotThisColumn = SUBSTITUTE('Table'[Display As],"This ","")And then countCALCULATE ( DISTINCTCOUNT(My Data[NotThisColumn]) )
3 Replies
Hi WorkHard
I would create a column as
NotThisColumn = SUBSTITUTE('Table'[Display As],"This ","")And then countCALCULATE ( DISTINCTCOUNT(My Data[NotThisColumn]) )
WorkHardAppreciate the idea but I'd like to avoid creating helper columns unless strictly necessary.
The way I solved it is like this:
CountResult = CALCULATE ( DISTINCTCOUNT(My Data[ID]), FILTER ( My Data, SEARCH ( "This", My Data[Display As],, 0 ) = 0 ) )- WorkHard
Ended up going with your original solution by creating an extra calculated column.
Filtering the measure produces unexpected results in some contexts.