Forum Discussion

hpayne93's avatar
hpayne93
Frequent Visitor
3 years ago
Solved

DAX with text filter

Hi,

 

I need to add a new measure to sum the total of newsletter sends. Currently my measure shows the total of ALL email campaign sends - this includes newsletters, webinar invites etc. 

 

Newsletter sends = CALCULATE(SUM('Mailchimp Campaign'[Send]), USERELATIONSHIP('Mailchimp Campaign'[Sent On],'Date'[Date]))
 
What do I need to add to my measure so I am only calculating sends where the 'name'(mailchimp campaign) column contains 'newsletter'?
 
Thanks!
General Comment
Need Help
Show and Tell
Tips and Tricks
  • vk_pbi's avatar
    vk_pbi
    3 years ago

    Try this

    CALCULATE(
SUM('Mailchimp Campaign'[Send]), 
FILTER(
	'Mailchimp Campaign',
	CONTAINSSTRING('Mailchimp Campaign'[Name],"newsletter")
),
USERELATIONSHIP('Mailchimp Campaign'[Sent On],'Date'[Date]))

5 Replies

  • vk_pbi's avatar
    vk_pbi
    Resolver II
    3 years ago

    Hi hpayne93 

    I havent' tested the below code, it should work

    CALCULATE(
SUM('Mailchimp Campaign'[Send]), 
FILTER(
	'Mailchimp Campaign',
	'Mailchimp Campaign'[Name] = 'newsletter'
),
USERELATIONSHIP('Mailchimp Campaign'[Sent On],'Date'[Date]))

     

    • hpayne93's avatar
      hpayne93
      Frequent Visitor
      3 years ago

      Hey, thanks for the reply!

       

      That doesn't quite work. I want to see the total where 'mailchimp campaign' [name] contains 'newsletter' -- do i need to use CONTAINSSTRING?

      • vk_pbi's avatar
        vk_pbi
        Resolver II
        3 years ago

        Try this

        CALCULATE(
SUM('Mailchimp Campaign'[Send]), 
FILTER(
	'Mailchimp Campaign',
	CONTAINSSTRING('Mailchimp Campaign'[Name],"newsletter")
),
USERELATIONSHIP('Mailchimp Campaign'[Sent On],'Date'[Date]))
  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    3 years ago

    To calculate the total number of newsletter sends, you can modify your measure by adding a filter condition using the CONTAINS function. Here's an example of how you can modify your measure to only calculate sends where the 'name' column contains 'newsletter':

    Newsletter sends =

    CALCULATE(

    SUM('Mailchimp Campaign'[Send]),

    USERELATIONSHIP('Mailchimp Campaign'[Sent On], 'Date'[Date]),

    CONTAINS('Mailchimp Campaign', 'Mailchimp Campaign'[name], "newsletter")

    )

    Thank you