Help with formula including SWITCH, TRUE, ISBLANK

[Anonymous]

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?

[Anonymous · Accepted solution]

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

[tamerj1]

Hi @Anonymous 

try to wrap it with MAX

[Anonymous]

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]

@Anonymous 

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

[Anonymous]

If you look at the screenshot above, you can see that COOKIE_ID is underlined in red.
The error message is in Dutch, I translated it above.

I cannot select COOKIE_ID in this formula.

[tamerj1]

@Anonymous 

But if you wrap it with MAX you should not receive an error asking you to provide aggregation of the column because MAX is an aggregator. This why I asked you to provide a screenshot of the measure code to mske sure no mistake in the code. However, please make sure the MAX function is placed inside the ISBLANK function not outside.