Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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_...
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    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's avatar
Whitewater100
Solution Sage
4 years ago

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