Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BINewbie1
Helper II
Helper II

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,

 

Adam

1 ACCEPTED SOLUTION

@BINewbie1 
Looking at this screenshot I instantly realized my mistake. We need to filter the complete table as the VALUES of the column will return only distinct values and duplicates won't be counted.

However, in the example that you have provided earlier, the first code would have produced 3 not 2. I will create a sample file to double check. For now please try

 

 

Count Referal Text =
COUNTROWS (
    FILTER (
        CROSSJOIN (
            pre_referral,
            VALUES ( 'Lookup BBBC_referalreasonquestions_health'[Code] )
        ),
        CONTAINSSTRING (
            pre_referral[bbbc_referralreasonquestions_health],
            'Lookup BBBC_referalreasonquestions_health'[Code]
        )
    )
)

 

 

*UPDATE

Attached is the sample file

1.png


Also make sure there is no relationship between the two tables. Some times it is automatically created without you even noticing. 

View solution in original post

16 REPLIES 16
BINewbie1
Helper II
Helper II

Yes so on a bit of a review of the data, the measure is not working as desired.

 

Its only counting the strings when they are in a cell on their own, not when they are part of a cluster of other strings in the same cell. Hence the numbers significantly under reporting.

 

I should add at this point that the strings, are seperated only by a comma, not by a space as well, as in my example before:

 

for example:

 

441690003,441690008
441690003,441690004,441690005,441690007,441690008
441690003
441690003

 

The current measure would show:

 

441690003 = 2 - for some reason only when the text string is on its own.

 

but it should count:

 

441690003 = 4 

 

Adam

@BINewbie1 

Strange. My proposed measure should give a result of 4. There must be another issue perhaps extra space. Also double check the order of columns inside CONTAINSSTING

I'm not great with coding generally, took be ages to work out I needed an extra comma to make one be of code work the other day.

 

Having looked up the syntax for CONTAINSTRING, its round the correct way.

 

I'll carry on trying to find a space.

 

Thanks,

 

Adam

@BINewbie1 

Can you please confirm which column that contains the comma delimited texts and which one is the unique distinct text values?

Thanks for sticking with it.

 

pre_referral[bbbc_referralreasonquestions_health] is the long list of comma deliminated strings, within the fact table.

 

Lookup BBBC_referalreasonquestions_health[Code], is the dimention table that contains the list of 10 codes alongside the corresponding name for the reason in plan english, which is called [Display].

 

I was a bit confused by which fields need to have the ' in the Dax.

 

@BINewbie1 

It should work 😅

can you help me out with some screenshots?

OK

 

Screen shot one is using the Measure Count Referal Text 2 using your Dax code, and the table displaying the data.

 

Screen Shot 1.jpg

 

and screen shot two is using the Measure Count Referal Text, which uses the Dax code I borrowed from youtube, which is a bit closer, but its only counting the strings when they appear on their own in a cell, not when in a long list of other strings.

 

Screen Shot 2.jpg

 

Hope that's viewable - if not i'll enlarge them.

 

Thanks!


Adam

Sorry here's a third screen shot with the fields atually as you wrote the code. I swapped them around just to check. But it made no difference.

 

Screen Shot 3.jpg

@BINewbie1 
Looking at this screenshot I instantly realized my mistake. We need to filter the complete table as the VALUES of the column will return only distinct values and duplicates won't be counted.

However, in the example that you have provided earlier, the first code would have produced 3 not 2. I will create a sample file to double check. For now please try

 

 

Count Referal Text =
COUNTROWS (
    FILTER (
        CROSSJOIN (
            pre_referral,
            VALUES ( 'Lookup BBBC_referalreasonquestions_health'[Code] )
        ),
        CONTAINSSTRING (
            pre_referral[bbbc_referralreasonquestions_health],
            'Lookup BBBC_referalreasonquestions_health'[Code]
        )
    )
)

 

 

*UPDATE

Attached is the sample file

1.png


Also make sure there is no relationship between the two tables. Some times it is automatically created without you even noticing. 

Thanks Tamerj1,

 

That seems to be working fine now.

 

There's was a relationship between the tables and turning it off seems to have done the trick.

 

I also now know that there's isn't a 10th code for other - suspect the free text for other is kept somewhere else in the system.

 

Many thanks for all you work - its really appreciated!,

 

Adam

@BINewbie1 

Check for spaces or hidden characters in the lookup table. You can use power query to trim and clean. Also mske sure the data type of bith columns is text. 

Thanks, its clean, and data type is the same.

 

I was wondering about the text appearing as a single long string. In the Measure I posted that seems to be how its behaving. Only counting the string when it appears on its own.

 

Is there maybe a wildcard character I could insert into the Dax to effectivly break up the long comma seperated fields. ? or *

 

 

BINewbie1
Helper II
Helper II

Hi tamerj1,

 

Thanks.

 

So in this field say there are 5 rows, which look like:

 

441690000, 441690001, 441690004, 441690006  

441690001, 441690003, 441690004, 441690006

441690002, 441690005
441690003, 441690005, 441690006, 441690008, 441690009
441690002, 441690003, 441690005, 441690007, 441690008, 441690009
 
I'd like to end up with a visualisation showing:
 
441690000 = 1 
441690001 = 2
441690002 = 2
441690003 = 3
441690004 = 2
441690005 = 3
441690006 = 3
441690007 = 1
441690008 = 2

441690009 = 2

 

Watching a youtube video I've come up with the following Measure, which seems to do something but maybe is under reporting, and seems to have a large number of null/blank fields, which maybe the data, or maybe the Measure.

 

Count Referal Text = COUNTROWS(CALCULATETABLE(pre_referral,FILTER(pre_referral,IFERROR(SEARCH(SELECTEDVALUE('Lookup BBBC_referalreasonquestions_health'[Code], ""), pre_referral[bbbc_referralreasonquestions_health]),-1)>0)))
 
Thanks,

Adam

 

@BINewbie1 

Assuming pre_referral[bbbc_referralreasonquestions_health] is the column that contains the comma delimit strings and 'Lookup BBBC_referalreasonquestions_health'[Code] is column from the disconnected table tyat contains the distinct unique values of the strings (also text data type not integer) which is the column placed in the table visual, then please try

Count Referal Text =
COUNTROWS (
FILTER (
CROSSJOIN (
VALUES ( pre_referral[bbbc_referralreasonquestions_health] ),
VALUES ( 'Lookup BBBC_referalreasonquestions_health'[Code] )
),
CONTAINSSTRING (
pre_referral[bbbc_referralreasonquestions_health],
'Lookup BBBC_referalreasonquestions_health'[Code]
)
)
)

 

Thanks for offering this.

 

It doesn't work i'm afraid, just a 1 value for each reason, and 144 for 151 blanks (no idea where that's coming from). Maybe its counting distictvalues?

 

The string value in the lookup table was actually set to an integer, but I changed it to text, but seems to make no difference to either measure.

 

Thanks again.

 

Adam 

tamerj1
Super User
Super User

Hi @BINewbie1 

yes possible with DAX. please present one example. 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.