Forum Discussion

BINewbie1's avatar
BINewbie1
Icon for Helper II rankHelper 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. 

BINewbie1's avatar
BINewbie1
Icon for Helper II rankHelper II
3 years ago

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

 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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]
    )
    )
    )

     

    • BINewbie1's avatar
      BINewbie1
      Icon for Helper II rankHelper II
      3 years ago

      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