Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count appearance of string in a comma-separated data column does not count values after the first co

I have two tables:   Products table has a single column with one product per field:           List table has several columns but the column I'm interested in has fields that can have as...
"FILTER"
CONTAINSSTRING
COUNTROWS
DAX
measure
Search
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    Please delete or deactivate that relationship or use 

    Customer Products at M0 =
CALCULATE (
    SUMX (
        VALUES ( Products[Product] ),
        SUMX (
            VALUES ( Query1[M0_LIST] ),
            INT ( CONTAINSSTRING ( Query1[M0_LIST], Products[Product] ) )
        )
    ),
    CROSSFILTER ( Query1[M0_LIST], Products[Product], NONE )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 

If the same product is repeated multiple times inside the string and you want to consider that in the counting then please use

Customer Products at M0 =
SUMX (
    VALUES ( Products[Product] ),
    SUMX (
        VALUES ( Query1[M0_LIST] ),
        VAR String = Query1[M0_LIST]
        VAR Items =
            SUBSTITUTE ( String, ",", "|" )
        VAR Length =
            COALESCE ( PATHLENGTH ( Items ), 1 )
        VAR T1 =
            GENERATESERIES ( 1, Length, 1 )
        VAR T2 =
            SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
        RETURN
            COUNTROWS ( FILTER ( T2, [@Item] = Products[Product] ) )
    )
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi tamerj1 , thank you. I tried using the first measure and still gave me the incorrect counts, using the second one gave me this result.

 

 

The same product is not repeated inside the string, but two clients can definitely have the same products but they would be listed in different rows.

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

    Anonymous 

    Can you please explain exactly what is the problem?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi tamerj1 , with your second measure I got a count of 1 for each product:

       

       

      Also, the List table contains blank values/rows too, and as for active relationships I see that the Product and Lists table are connected in the Product[Products] column and the List[M0_LIST] column, one to many relationship. I don't know if this is causing the issue and hope this gives more clarity to my issue.

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

        Anonymous 
        Please delete or deactivate that relationship or use 

        Customer Products at M0 =
CALCULATE (
    SUMX (
        VALUES ( Products[Product] ),
        SUMX (
            VALUES ( Query1[M0_LIST] ),
            INT ( CONTAINSSTRING ( Query1[M0_LIST], Products[Product] ) )
        )
    ),
    CROSSFILTER ( Query1[M0_LIST], Products[Product], NONE )
)