Forum Discussion

Ilh's avatar
Ilh
New Member
8 years ago

Column using two tables

Hello all, 

 

I am facing an issue with my measure, and i hope you can help me.

I am trying to do a column using two tables named "Contacts" & " Email Results"

My column is Brand engagement and it's categorizing the email results per contact using how many email is opened

less than 20% 

Between 20-50%

Between 50-70% 

More than 70% 

Brand Engagement = if('Email Results'[EPR]<0.2,"Not Interested <20%",
If('Email Results'[EPR]=0.2 ||'Email Results'[EPR]>0.2 && 'Email Results'[EPR]<0.5,"Somewhat (between 20% and 50%)",
If(
('Email Results'[EPR]=0.5 ||'Email Results'[EPR]>0.5) && 'Email Results'[EPR]<0.7,"Interested (between 50% and 70%)",
If(
'Email Results'[EPR]>=0.7,"Engaged >70%"))))

The issue is that it's not working when i choose a year as a filter (2017 or 2016), it gave me sometimes for a contact with 70% of email opened not interested :(

 

The date of email opened and send are in the table of email results and not in the contact. 

 

Thank you in advance for your help :)

 

 

Need Help

3 Replies

  • sanimesa's avatar
    sanimesa
    Post Prodigy
    8 years ago

    Can you post some sample data? My hunch is you might need to use a measure rather than a column. 

    • Ilh's avatar
      Ilh
      New Member
      8 years ago
      Email Send IDEmail Open flagContact ID
      a2324000500nKcud10000025152dfgr
      a2324000000nKcz0a2324000000nKd2
      a23524000000nKcj1a2324000000nKbjg
      a2324000000nKd90a2324000000nKbH
      a2324000000nKcF1a2324000000nKbMg
      a2324000000nKbgd0a2324000000nKorg
      a2324000000nKd200000025152dfgr
      a2324000000nKbjg0a2324000000nKd3
      a2324000000nKbH0a2324000000nKbjg
      a2324000000nKbMg0a2324000000nKbH
      a2324000000nKorg0 

       

      Email Send Id and Open flag are in the Email results Table 

      And Contact in another Table

       

      This is the column i have :

       Column : Brand Engagement = if('Email Results'[EPR]<0.2,"Not Interested <20%",

      If('Email Results'[EPR]=0.2 ||'Email Results'[EPR]>0.2 && 'Email Results'[EPR]<0.5,"Somewhat (between 20% and 50%)",
      If(
      ('Email Results'[EPR]=0.5 ||'Email Results'[EPR]>0.5) && 'Email Results'[EPR]<0.7,"Interested (between 50% and 70%)",
      If(
      'Email Results'[EPR]>=0.7,"Engaged >70%"))))

       

      And the EPR as a measure 

      EPR = CALCULATE('Email Results'[SUMEMAILOPEN]/'Email Results'[COUNTSENDID])

       

      I thought about the measure but then i can not use the "brand egagement " as a Slicer or a filter Page...

       

      Thank you for your help in advance :)