Counting multiple matches

[Ben_G]

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

Campaign	Format
C10	Digital
C11	HPP
C12	Digital HPP
C13	HPP
C14	HPP SPP
C15	SPP
C16	Digital Others
C17	Digital HPP Others
C18	Others
C19	Others SPP
C20	SPP

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

Campaign	Format	Count of Formats
C10	Digital	1
C11	HPP	1
C12	Digital HPP	2
C13	HPP	1
C14	HPP SPP	2
C15	SPP	1
C16	Digital Others	2
C17	Digital HPP Others	3
C18	Others	1
C19	Others SPP	2
C20	SPP	1
		17

b) Count of Formats

Format	Count
Digital	4
HPP	5
SPP	4
Others	4
	17

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

[Ben_G]

@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 

[Icey]

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]

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]

@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.


 

[Fowmy]

@Ben_G 

I used Power Query to split the formats, please check the attached file.