Create a measure for a count of a distinct count

[flights]

Hi Guys,

I am trying to measure repeat customers based on email addresses. As some customers make multiple purchases on the same day, I have added another column which combines date and email address called EmailDate I have a matrix which then looks at the email address, and the distinct count of EmailDate, and therefore any which returns 2 and over are repeat customers for the period I am looking at. So for example - 

The original data looks like

Email                                                   Booking Date                         EmailDate

Dave@address.com                            11/12/2018                            Dave@address.com 11/12/2018

Dave@address.com                            11/12/2018                            Dave@address.com  11/12/2018

Dave@address.com                             08/12/2018                           Dave@address.com  08/12/2018

Dave@address.com                             09/12/2018                           Dave@address.com  09/12/2018

etc etc etc. 

And so my matrix looks like - 

Email                                                  (Distinct) Count of EmailDate

Dave@address.com                                3

Meg@address.com                                 2

Jane@address.com                                 1

I need a measure of all of the results from the matrix that are 2 and above, so that I can calculate how many customers are repeat customers in the period. 

Hope that makes sense! 

Cheers 

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=COUNTROWS(FILTER(SUMMARIZE(VALUES(DATA[Email]),[Email],"ABCD",DISTINCTCOUNT(Data[Booking Date])),[ABCD]>=2))

Hope this helps.

[flights]

THANKYOU SO MUCH!!!!!!!!!! Ashish you're a legend. 

[Ashish_Mathur]

You are welcome.

[v-juanli-msft]

Hi @flights

Create a measure

Measure = CALCULATE(DISTINCTCOUNT(Sheet2[Booking Date]),ALLEXCEPT(Sheet2,Sheet2[Email ]))

Best Regards

maggie