Forum Discussion

BINewbie1's avatar
BINewbie1
Helper II
3 years ago
Solved

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 tex...
Counting comma deiminated text strings
  • tamerj1's avatar
    tamerj1
    3 years ago

    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


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

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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


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

BINewbie1's avatar
BINewbie1
Helper II
3 years ago

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