Count rows that contain certain text logic

[Anonymous]

Hi all, I am trying to count the channels that a customer has purchased. The tricky part is that I do not want to double count the number Channels a customer has used. For example, for customer A, I want to say that they have purchased 3 channels (Email, TV, Mail). For customer B, I want to say that they have purchased 5 channels (TV, Phone, plus the 3 channels from Premium)

The logic I want would be something along the lines of: If a customer purchased a Channel containing the string "email", add 1 channel to the count (but no duplicate counting for email - the customer can only have one email channel). If Channel contains the string "TV", then add 1 channel to the count. If Channel contains the string "Premium", add 3 channels to the channel count (but don't count Premium channels twice if it shows up twice like for customer B). Any ideas for how to do this?

Customer	Channel
A	Email
A	Email Plus
A	TV Plus
A	Mail
B	TV
B	Email
B	Premium (3)
B	Premium (3)
B	Phone

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the following measure to meet your requirement.

Measure = 
IF (
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', CONTAINSSTRING ( 'Table'[Channel], "Email" ) )
    ) > 0,
    1,
    0
)
    + IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( 'Table', CONTAINSSTRING ( 'Table'[Channel], "TV" ) )
        ) > 0,
        1,
        0
    )
    + IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( 'Table', CONTAINSSTRING ( 'Table'[Channel], "Premium" ) )
        ) > 0,
        3,
        0
    )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[Anonymous]

Hi @v-zhenbw-msft , thanks a lot for the help. I have one additional question. For customers who purchased Premium channel AND an email channel, I do not want to count the email channel. Is it possible to account for this in the logic? For example, customer B in the example data would have purchased 4 channels instead of 5.

[amitchandak]

@Anonymous , you can use distinct count

distinctcount(Table[channel])
calculate(distinctcount(Table[channel]), Table[channel] = "Email Plus") //with filter
calculate(distinctcount(Table[channel]), Table[channel] in{ "Email Plus", "Email"}) //with two filter

// GT will not be correct, to get correct GT

sumx(summarize(Table, table[customer], "_1", distinctcount(Table[channel])),[_1])// replace distinctcount(Table[channel] with final formula

[mahoneypat]

This seems like a good application for a Groups column.  See the link below.  Basically, you can add a column to your model in which you group the related Channel values together (e.g., Email and Email Plus).  You can then do a DistinctCount() on that new column to count how many each customer purchased.

https://datachant.com/2016/10/31/column-grouping-power-bi-desktop-october-update/

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.
Regards,
Pat

[Anonymous]

Hi @mahoneypat , thanks for the help. The issue is, I actually have hundreds of different channel names...for example there are 20+ with the string "email" in them and it can changes over time. Is there any way to group all values that contain "email" together, and also group all values that contain "tv" together? I would also then need to assign Premium as 3 channels and then count the number of channels purchased by customer.

[mahoneypat]

Two other things you can try.  Both require you to make a table with the list of consolidated channel terms (e.g., Email, TV) called "Lookup".

1. Merge this new table into your existing table on the two related fields, and check the fuzzy matching box and put a low threshold (I got it to work with a small table with a value of 0.3)

2. Load this new table but have no relationship.  Add a calculated column to your initial table with an expression like this:

LookupWord = var currentchannel = Channels[Channel]

return CALCULATE(MIN(Lookup[Word]), FILTER(Lookup, CONTAINSSTRING(currentchannel, Lookup[Word])))

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.
Regards,
Pat