Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help with formula including SWITCH, TRUE, ISBLANK

Hey guys,

I have this column called COOKIE_ID and a column VISIT_ID.
I want to figure out if a visitor (identified by COOKIE_ID) comes for the first time or is a returning visitor or an annonymous visitor.

For this I figured I write the following formula:

Cookie Status =
VAR SelectedDateRange =
    DATESBETWEEN ( 'OD - All Page Visits Portal'[PAGE_LOAD_DATE], MIN ( 'OD - All Page Visits Portal'[PAGE_LOAD_DATE] ), MAX ( 'OD - All Page Visits Portal'[PAGE_LOAD_DATE] ) )
VAR CookieVisits =
    CALCULATETABLE (
        VALUES ( 'OD - All Page Visits Portal'[COOKIE_ID] ),
        ALLEXCEPT ( 'OD - All Page Visits Portal', 'OD - All Page Visits Portal'[COOKIE_ID] ),
        SelectedDateRange
    )
VAR VisitCount =
    CALCULATE (
        DISTINCTCOUNT ( 'OD - All Page Visits Portal'[VISIT_ID] ),
        ALLEXCEPT ( 'OD - All Page Visits Portal', 'OD - All Page Visits Portal'[COOKIE_ID] ),
        SelectedDateRange
    )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( 'OD - All Page Visits Portal'[COOKIE_ID] ), "Anoniem",
        VisitCount > 2, "Terugkerend",
        VisitCount = 1, "Nieuw",
        "Onbekend"
    )


For some odd reason though I can't select the column COOKIE_ID in ISBLANK.

See picture below


What am I doing wrong here?

isblank
Need Help
switch
True
  • Anonymous's avatar
    Anonymous
    3 years ago

    Silly me... I was using a measure instead of calculated column.

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hey Tamer,

      Even if I wrap it in MAX I get the following error:

      A single value for column 'COOKIE_ID' in table 'OD - All Page Visits Portal' cannot be determined. This can happen if the measure formula refers to a column that contains many values and no aggregation such as min, max, count, or sum has been specified to obtain a single result.

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Anonymous 

        That is strange. Would you please paste the a screenshot of the formula along with the error message?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Silly me... I was using a measure instead of calculated column.