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 )
)
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 )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    That was it! Thanks a lot tamerj1