DAX to mark all rows where two conditions are met, one in another table

[Anonymous]

Morning hive mind!

been struggling to get my head around this one, but am pretty sure it's fairly simple....

I have two tables, a Contacts table and a Customer table, as below;

CONTACTS

Contact ID

EMail

Customer ID

Is Duplicate Email (Y/N)

CUSTOMERS

Customer ID

Is target customer (Y/N)

I want to add a new boolean column to the CONTACTS table which allow me to filter for Duplicate Email addresses where at least one of these Contacts belong to a Target Customer list.

Not all Customers are on this list, and not all emails are duplicates.

Hopefully this is enough info to be able to help with with the DAX for the new column in the Contacts table?

Thanks in advance

Chris

[Anonymous · Accepted solution]

Managed to resolve this myself in the end!  😀  Thanks for your input though @Anonymous - kudos.

Step1:

Created a new column in Contacts table to flag Contacts belonging to a Target customer

Target Customer = Related(Customer[Is Target Customer])

Step 2:

Created a new table that lists all Contact Emails meeting the criteria.

Target Customer Dupe Emails =
FILTER(
   ALL(
      Contacts[Email], 
      Contacts[Is Duplicate Email], 
      Contacts[Target Customer]
      ),
Contacts[First Look - Confirmed] = "Y" 
&& Contacts[Contact Has Duplicate Email] = "Y")

 Step 3:

Created a new boolean column in Contacts table that can then be used to filter as required.

Target Dupe Email = 
IF(
   COUNTROWS(
         FILTER('Target Customer Dupe Emails',
             'Target Customer Dupe Emails'[Email] = Contacts[Email]
             ))>0,"Y","N")

[Anonymous]

Hi @Anonymous 

According to my understanding you have 2 tables and you would like to find out "which email has a duplicate value and at the same time belongs to the target customer list".

Table 1 : Contacts

Table 2 Customers

Step 1: Map relationship between Customer ID for both tables.

Step 2: In Table 1 add a new calculated Column, this would tell us if there is a duplicate for the emails or not

Duplicate Y/N = 

IF(
    CALCULATE(
        COUNT(Contacts[Contact Email])>=2),
        "Y","N"
)

Step 3: Add a new calculated column in Table 1:

In Target Customer Table? = RELATED(Cutomers[Target Customer])

Step 4: End Result. The blank in the 4th column shows that the customer klm@gmail.com was not present in the customers table.

I hope that helps. 

[Anonymous]

Hi @Anonymous 

Thanks for your reply.  

That's not quite it, but the example tables in my original post didn't render correctly on the page so that's entirely my fault - sorry!

The tables are actually as below;

I want to add a new boolean column to CONTACTS that will allow me to filter for all Contacts where the following conditions are true;

1. Is Duplicate Email = Y

2. At least one of the Customer ID is flagged as Is target Customer = Y

The resultant filtered table would include Contact records where the email address is a duplicate and associated with a target customer AND all rows containing a duplicate email even when the Customer ID is NOT a target customer, i.e. I want to see a list of ALL Contacts where the duplicate email address is associated with at least one Customer that is a target customer. 

Resultant Contacts table would look something like this;

Contacts 1 & 2 would both be highlighted as, even though Contact 1 is associated with Customer 3 which is NOT a target Customer, it shares an email address with a Contact that IS associated with a target customer, i.e. Customer 4.

Hope that makes sense!!

[Anonymous]

Here is how the final table looks like, the steps are the same as the previous table except the data is different. 

I Hope that helps.

[Anonymous]

In your example final table, contact ID 4 should not be marked as Y in the final column becuase it is not a duplicate contact.

[Anonymous]

I get the feeling it might have something to do with counting the total rows for duplicate contacts where RELATED('Customers'[Is target customer]) and where this is >0 for a given email address marking it as Y....?

[Anonymous]

Sorry for dragging this. 

Are you referring to Contact ID 4? And why is is being marked as Y in the 4th column. 

[Anonymous]

Sorry, I'm not sure I follow.

You previous response includes a step to identify duplicate email addresses in the Contacts table, but I've already added a column that flags these.

There is also an existing relationship between both tables via the Customer ID.

Also, although I can add a column as per your step 3, in my example, this would only flag Contact 2 as this is the only Contact that belongs to a Target Customer.

I want to flag Contacts 1 & 2 becuase they share an email address that belongs to at least one Contact associated with a Target Customer.  I need to be able to see Contact 1, even though this is associated with a non-Target customer.