Forum Discussion

Ben_G's avatar
Ben_G
Frequent Visitor
4 years ago

Counting multiple matches

Hi. New to this forum so hope my post is clear.

I have a table of campaign formats:

Format
Digital
HPP
SPP
Others

I have a table of campaigns which can have 1 or more formats (NB not separated by commas or similar):

CampaignFormat
C10Digital
C11HPP
C12Digital HPP
C13HPP
C14HPP SPP
C15SPP
C16Digital Others
C17Digital HPP Others
C18Others
C19Others SPP
C20SPP

I want to be able to calculate:
a) Numbers of formats per campaign:

CampaignFormatCount of Formats
C10Digital1
C11HPP1
C12Digital HPP2
C13HPP1
C14HPP SPP2
C15SPP1
C16Digital Others2
C17Digital HPP Others3
C18Others1
C19Others SPP2
C20SPP1
  17

b) Count of Formats

FormatCount
Digital4
HPP5
SPP4
Others4
 17

I have tried numerous variations of Contains, Search, Find, Filter but have not yet found a solution that works. Any suggestions gratefully received.

Need Help

5 Replies

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi Ben_G ,

     

    Can this work in your scenario?

    Measure =
SUMX (
    VALUES ( FormatTable[Format] ),
    CALCULATE (
        COUNTROWS (
            FILTER (
                ( CampaignTable ),
                CONTAINSSTRING ( CampaignTable[Format], MAX ( FormatTable[Format] ) )
            )
        )
    )
)

     

     

     

    Best Regards,

    Icey

     

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

  • Ben_G's avatar
    Ben_G
    Frequent Visitor
    4 years ago

    Hi Fowmy Thanks for the prompt response.
    Additional info. I provided a small subset of data. However some of the entries in my full list of campaigns comprise 2 words (eg New Channel) so splitting by space delimiter may not be effective.
    Any thoughts?

    • Fowmy's avatar
      Fowmy
      Super User
      4 years ago

      Ben_G 

      There has to be a logic and consistency in that case. If you can somehow identify the list of campaigns, it will solve the problem.
      Please let me know if you have any suggestions as you are familiar with your dataset.


       

  • Ben_G's avatar
    Ben_G
    Frequent Visitor
    4 years ago

    Icey 

    That worked perfectly. Thanks very much.
    Could you possibly help me with the next level of complexity?

    Each campaign has a value and I want to be able to calculate the value per format exactly as per the way formats has been done, ie any campaign that includes one of the selected formats. PBIX file linked below. Thanks in advance ðŸ¤ž

    PBIX file