Calculated Column Counting Distinct Values with a Filter

[Anonymous]

Hey everyone,

My first post here and here is the situation:

I'm making a dashboard regarding webpage visits.
Every visitor has a unique cookie_id.
Every visit of a visitor has a unique visit_id.
Every visit also has a unique timestamp.
I'd like to add a column that tells me if a visitor is a new one, or a recurring visitor.
A recurring visitor is if the cookie_id has more than 1 unique visit_id's (in the filtered timeframe).

Let's say the filter is set on dates between 1-1-2022 and 31-3-2022.
If someone visited the webpage twice in january my column should return: "Recurring"
If someone visited the webpage once in march my column should return: "New"
If someone visited the webpage once in march and once on 15-4-2022, my column should return: "New" even though the amount of visits are counted as 2, due to the filter above.

Is this possible? If so, how?

With kind regards,

LaZZaNoVa

[Anonymous]

The measure works perfectly. However I needed to have a column added with the values "New" and "Recurring", instead of adding these values to a matrix. Is it possible to add a calculated column that does the same as the measure?

The HASONEVALUE doesn't seem to work in a calculated column. IT returns False even if you only have 1 row, 1 cookie_id.

Eventually I wanted to visualize the percentages of New and Recurring Visit_types in a pie chart. 

[Whitewater100]

Hi:

Here is how the pie chart can be created:

[Whitewater100 · Accepted solution]

Hi:

The calculated column can be 

Visit Type =

VAR visitct = CALCULATE(COUNT(Data[VisitID]), ALLEXCEPT(Data,Data[Cookie ID]))

return

IF(visitct = 1, "New", IF(visitct > 1, "Recurring", BLANK()))

However, you will want to use measures as often as possible as best practice. But for sort fields they are great. Here is pie chart using calc columns:

I hope this solves the question!

[Whitewater100]

Hi:

Yes this is possible. Please see attached link. If you move the date slicer to 4-05-2022 you will see a new visit that does not appear when date slicer is just through March. If you have a list of vistors that are ID'ed by Cookie number you could have another dimension table called vistors which would join on Cookie ID but that is a potential suggestion for the future. I hope this solves your question.. Link below.

https://drive.google.com/file/d/1oHG0Aaj3MW2eZOC4Cj9i_IMbc8Vv6JhT/view?usp=sharing