Count multiple instances of a text string in a field

[scabezas]

Hi

is there a way to count multiple instances of a string in a text field. In this example, I need to count the 2 strings CR-APPROVED for the number of approved requests for project Take over the world. I have used in the past CONTAINS to count a string but it returns only one instance in the count and not both. Also, I am using a Power BI Service Live Connection to connect to the data source so it is a bit limited on what I can do.

 

[v-stephen-msft]

Hi @scabezas ,

The principle is to delete CR-APPROVED and calculate the difference between the two strings. This difference divided by the length of CR-APPROVED is the result. And it works in live connection.

Try this measure

Measure = var _len=LEN(MAX('Table'[Change reques approvals]))
var _len1=LEN(SUBSTITUTE(MAX('Table'[Change reques approvals]),"CR-APPROVED",""))
RETURN DIVIDE( _len-_len1,LEN("CR-APPROVED"))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[scabezas]

Hi Stephen Tao, thank you for taking the time to respond and add the PIBIX file, that helps a lot. I updated the PIBIX file to include a second table with 3 records where the Redirection Comments field includes actual data and where the count of of instances in each record for the key word PCR-APPROVED is 2 and the goal is to report the total count; in this case, would be 6; however the measure in your proposed solution returns a count of 2 as shown in the card and table totals.  Is there a way to get the total count expected of 6 instances?

 I do not see an option to upload the updated PIBIX for reference

[v-stephen-msft · Accepted solution]

Hi @scabezas ,

It's easy to complete it. Try 

Total count = SUMX(ALL('Table'),[Measure])

You can view the details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[scabezas]

@v-stephen-msft Thank you so much for the solution. The PIBIX with the actual solution helps greatly. I have implemented the solution and is working like a charm

[CNENFRNL]

Simple enough in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlHwS8xNVdJRCi5Nys0sKUlNiclzDtJ1LCgoyi8DcZDEPfMUnDNSk7NRVCjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Process = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Process", type text}}),
    Count = Table.AddColumn(#"Changed Type", "Occurrence", each List.Count(Text.PositionOf([Process], "cr-approved", Occurrence.All, Comparer.OrdinalIgnoreCase)))
in
    Count

[selimovd]

Hey @scabezas ,

you can solve that with SUMX and CONTAINSSTRING:

Amount Approved =
SUMX(
    myTable,
    IF(
        CONTAINSSTRING(
            myTable[Change Requests Approvals],
            "APPROVED"
        ),
        1,
        BLANK()
    )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[scabezas]

Hi Denis, thank you for your response. I tried your suggested solution but does not give me the total expected number of instances for this field. The screenshot belows shows the measure (PCR-APPROVED Count) returns a count of 1 for each record where it should be 2 and the card shous the total unique instanes of 3 when it is expected to be 6. Is there a way to loop within a field to increase the instance count so that for each record in this screenshot would return 2 and therefore the card would total to 6?