CODEWITHSUNDEEP
powerbidax
Sebastian Hubard
Sebastian Hubard

Reputation: 163

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

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

Upvotes: 1

Views: 655

Answers (1)

CR7SMS
CR7SMS

Reputation: 2584

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.

Upvotes: 1

Related Questions