topic Help with formula including SWITCH, TRUE, ISBLANK in DAX Commands and Tips

Help with formula including SWITCH, TRUE, ISBLANK

Anonymous — Wed, 29 Mar 2023 18:15:51 GMT

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?

Re: Help with formula including SWITCH, TRUE, ISBLANK

tamerj1 — Wed, 29 Mar 2023 18:33:24 GMT

Hi Anonymous

try to wrap it with MAX

Re: Help with formula including SWITCH, TRUE, ISBLANK

Anonymous — Thu, 30 Mar 2023 07:43:35 GMT

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.

Re: Help with formula including SWITCH, TRUE, ISBLANK

tamerj1 — Thu, 30 Mar 2023 08:06:34 GMT

Anonymous

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

Re: Help with formula including SWITCH, TRUE, ISBLANK

Anonymous — Thu, 30 Mar 2023 11:52:17 GMT

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.

Re: Help with formula including SWITCH, TRUE, ISBLANK

tamerj1 — Thu, 30 Mar 2023 13:52:09 GMT

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.

Re: Help with formula including SWITCH, TRUE, ISBLANK

Anonymous — Tue, 04 Apr 2023 11:12:11 GMT

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