Power BI Custom Column: Matching Dates and Customer ID in Different Tables

1
votes

I'm currently working with some email data and in this data I have two tables: "Sends" and "Opens". Both tables have the date of the send or open by Customer ID.

Example Image

Ideally, I'd like to designate in a new column if a send date matches that of an open date for that specific customer.

Any help would be greatly appreciated. Thanks

1
powerbidax

1 Answers

1
votes

You can do this by using the following steps:

Create a concatenated field of Customer ID and Event Date in both the "Sends" and "Opens" table:

Key = CONCATENATE(Opens[Customer ID],Opens[Event Date])
Key = CONCATENATE(Sends[Customer ID],Sends[Event Date])

Create a relationship between the "Sends" and "Opens" tables by using the key variables. You can do this by selecting "Manage Relationships" under the "Modelling" tab.

Create a calculated column in the "sends" table using the following formula:

Open Date = IF(RELATED(Opens[Customer ID])=BLANK(),"No","Yes")

This should give you the desired result. Hope this helps.