Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 🤞
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.
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?
@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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Ben_G
I used Power Query to split the formats, please check the attached file.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |