Hey folks,
I know that this topic was already discussed, but I didn't get any of the solutions to work that were posted here before. I was able to solve my problem with 2 dax tables + measure, but I'm not happy with my solution and I guess that there is a better / simpler way to do it.
So let's take a look:
Imagine you have this type of data: an id, longitude, latitude (both does not really matter here) and date + time stamp.
did lon lat ltime
1 8.89314365386963 49.2425270080566 2018-08-02 20:58:12
1 8.89325141906738 49.2425804138184 2018-08-02 20:53:54
1 8.89325523376465 49.2425384521484 2018-08-02 21:00:50
1 8.89333343505859 49.2424774169922 2018-08-04 18:26:42
2 8.89333820343018 49.2424545288086 2018-07-30 20:08:41
3 8.89305019378662 49.2425384521484 2018-08-03 13:31:26
3 8.8930549621582 49.2425498962402 2018-08-03 13:30:27
3 8.89305782318115 49.242618560791 2018-08-03 13:28:25
3 8.89305782318115 49.2426338195801 2018-08-03 13:27:24
3 8.89306926727295 49.2425994873047 2018-08-03 13:29:26
3 8.89320182800293 49.2427368164063 2018-08-03 13:26:24
3 8.89355945587158 49.2428245544434 2018-08-03 13:25:32
3 8.89356231689453 49.2427139282227 2018-08-03 13:23:31
3 8.8935661315918 49.2427444458008 2018-08-03 13:24:22
What I want to do is: count how many people (distinct did) do exist with a different timestamp (based on 2 different days). If we take a look at did 1 - the first 3 rows count as one visit, the 4th row is a different day (timestamp does not matter) so it also counts as a visit. So this guy visited 2 times ( or >1) and represtens a returning customer. did 2 visited only once, so it does not count as a returning customer. did 3 visited the place several times (or stayed there a bit longer), but it was on the same day, so it does not count as a returning customer. So basically for that given data the measure would return 1. We have only 1 returning customer. I hope you did understand the problem.
My solution: first dax table
countPerDay = CALCULATETABLE (
SUMMARIZE (
KraichgauNord;
KraichgauNord[did];
'Date'[Date];
"returning customer"; COUNTROWS ( KraichgauNord )
)
)
the table returns the did, the date and the count of visists during that single day. So it's basically a group by did and date.
2nd step was to create the 2nd dax table:
returner =
SUMMARIZE (
countPerDay ;
countPerDay[did];
"real returning customers"; COUNTROWS (countPerDay)
)The 2nd table groups the 1st table by did. If you have a count of 1, the person has been once there on a single day. If it has a value of > 1, the person visited twice or even more on DIFFERENT days.
And finally I've made a measure which looks like this:
ct_returner =
var ct = CALCULATE(COUNTROWS(returner); returner[real returning customers] > 1)
return IF (ct = 0; 0; ct)
It seems to be so complicated.. I had to create 2 dax tables + measure for the simple count if a did exists more than once per day in a table.
Any ideas?
