Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Xboraxe
Frequent Visitor

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:

 

Capture.PNG

 

 

 

 

List table has several columns but the column I'm interested in has fields that can have as little as one product to as much as 17, all separated with a comma and no spaces in between:

 

Capture1.PNG

 

 

 

The issue I'm facing and need help with is to correctly count each product either, ideally as as a measure. I tried two measures but they both gave me an incorrect count of blanks; there are no blank values either table.
What I found is that both measures count only the *first* value they find in the List column and then continue counting subsequent rows:

 

Capture2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How can I correctly count all values in each field from the List table and have zero "blank" counts?

 

Thanks!

1 ACCEPTED SOLUTION

@Xboraxe 
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 )
)

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

Hi @Xboraxe 

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

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.

 

Capture01.PNGCapture02.PNG

 

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.

@Xboraxe 

Can you please explain exactly what is the problem?

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

 

Capture02.PNG

 

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.

@Xboraxe 
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 )
)

That was it! Thanks a lot @tamerj1 

tamerj1
Super User
Super User

Hi @Xboraxe 

please try

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.