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. 

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

BINewbie1 

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

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

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.

 

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

    BINewbie1 

    It should work 😅

    can you help me out with some screenshots?

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

      OK

       

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

       

       

      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.

       

       

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

       

      Thanks!


      Adam

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

        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.