Forum Discussion
Calculated Column Counting Distinct Values with a Filter
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
Hi:
The calculated column can be
Visit Type =VAR visitct = CALCULATE(COUNT(Data[VisitID]), ALLEXCEPT(Data,Data[Cookie ID]))returnIF(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!
4 Replies
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
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.Hi:
The calculated column can be
Visit Type =VAR visitct = CALCULATE(COUNT(Data[VisitID]), ALLEXCEPT(Data,Data[Cookie ID]))returnIF(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!
Hi:
Here is how the pie chart can be created: