counting comma deiminated text strings
1 Topic
Counting comma delimited text strings
Hi there, I have a field in my data model that represents a reason for something occuring. The field is is made up of between zero and up to nine strings of numbers, the field is formatted as text. The string corresponds to a text field in a look up table. In the data model there are nine 'reasons'. Each string is nine digits long (using the digits 0-9), and within each cell there can be multiple reasons for the occurance, each separated with a comma. I think at most all nine of the reasons might be present in a single cell, but the same reason will not be there more than once. I need to count the number of each of the 9 reasons across the rows, and present that in the visualisations as a table using the actual text 'reason' from the look up table. It obviously needs to be filterable by various other fields in that table - date, and a few categories. I've set up the relationship betweeen the two enities and feel fairly confident in that part. Any idea about how to do this, I'm reluctant to split the reasons into differernt columms as that seems messy, but if that's the best way. Do I need a measure for each of the nine reasons, that also seems messy - and a bit time consuming. Either a DAX code or suggestion in Power Query. Any ideas? Many thanks, AdamSolved4.2KViews0likes16Comments