Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count rows that contain certain text logic

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?

 

CustomerChannel
AEmail
AEmail Plus
ATV Plus
AMail
BTV
BEmail 
BPremium (3)
BPremium (3)
BPhone

 

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

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

      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's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        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
         
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

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

      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.