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!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Whitewater100
    Solution Sage
    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:

    Here is how the pie chart can be created: