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 

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

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

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

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

    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. 

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

      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 *

       

       

  • 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?