cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Alessandro-laba
Frequent Visitor

First date fro groups of emails

Hi there, 

 

I am trying to develop a dahsboard to report on our Marketing effectiviness. As part of this porject I want to show the best day to email our contacts to improve our email open rate. To do so I am trying to show when a contact is more inclined to open an email based on historic values. 

 

Let's say I have Contact A and that I sent him 10 emails over a year. I would like to see when these emails were opened, maybe contact A opens his emails on a Thursday so it would make sense send him the emails on that day. 

 

Now I have developed something but it is not working as I want. For email A I can see that Contact A actually opened it for the first time on a Thursday. The problem is that my dashboard is showing me that he opened the same email even on a Monday and on a Saturday and this would create problems with my next goal to measure the open rate. 

 

Does anybody know how I can select only the first time the email was opened? I have found the following formula: DayOpens = CALCULATE(FIRSTDATE(Eloqua_EmailOpen[CreatedAt])) but it is still wrong cause it still shows me the opens for Monday and Saturday.

 

If I put it in plain english the formula that I would like to have is: 'Show me only the first date the email was opened relalated to the email that was sent'. 

 

The data for Email_sent and Email_open are on 2 different data set but they are linked to eachother by Unique email address. 

 

Please let me know if anybody has any idea how to tackle this.

 

Thanks in advance, 

Alessandro 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: First date fro groups of emails

That is incredibly too little information to go on. "Doesn't work". It works for me, see attached PBIX.

 

I will need an explantion of "Doesn't work".


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: First date fro groups of emails

Well, one comment is that I think your use of CALCULATE is meaningless in that formula since you do not supply a filter clause. Second, try using MINX to get your date. With all that said, sample data and expected output are always extremely helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Alessandro-laba
Frequent Visitor

Re: First date fro groups of emails

Hi Greg,

 

These are the data: 

 

Email sent:

Data Send.PNG

 

Email Open:

Open.PNG

 

This is the day filter I have in my dashboard:

Day filter.PNG

So for Email A when I click on Tuesday I want to see the date value while when I click on Wednsday I don't wanna see any value for Email A since the first opened happened on the day before.

 

Thanks, 

Ale

Super User IV
Super User IV

Re: First date fro groups of emails

Right, so in your Email Open table, create a calculated column like this:

 

First Open = 
  WEEKDAY(
    MINX(
      FILTER(
        ALL('Email Open'),
        [Email name] = EARLIER([Email name]) &&
          [ContactID] = EARLIER([ContactID])
      ),
      [Emai Open]
    )
  )

Then just tie your slicer to that column.

 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Alessandro-laba
Frequent Visitor

Re: First date fro groups of emails

Hi @Greg_Deckler

 

It seems it is not working. Do you have any other suggestion? 

 

Thanks, 

Ale

Super User IV
Super User IV

Re: First date fro groups of emails

That is incredibly too little information to go on. "Doesn't work". It works for me, see attached PBIX.

 

I will need an explantion of "Doesn't work".


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors