Column using two tables

[Ilh]

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 🙂

[sanimesa]

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

[Ilh]

Email Send ID	Email Open flag	Contact ID
a2324000500nKcud	1	0000025152dfgr
a2324000000nKcz	0	a2324000000nKd2
a23524000000nKcj	1	a2324000000nKbjg
a2324000000nKd9	0	a2324000000nKbH
a2324000000nKcF	1	a2324000000nKbMg
a2324000000nKbgd	0	a2324000000nKorg
a2324000000nKd2	0	0000025152dfgr
a2324000000nKbjg	0	a2324000000nKd3
a2324000000nKbH	0	a2324000000nKbjg
a2324000000nKbMg	0	a2324000000nKbH
a2324000000nKorg	0

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 🙂

[sanimesa]

@Ilh  What you have provided does not help in understanding the data enough. 

However, you can also use the paremeter option to slice your data for different ranges, 

https://docs.microsoft.com/en-us/power-bi/desktop-what-if