Purchase recency with DirectQuery

[rgjiang]

Hello everyone,

I am trying to calculate the purchase recency by months using data from Google BigQuery via DirectQuery.

For this, I created this measure:

recency =

VAR dateToday = TODAY()

VAR daysDiff =

DATEDIFF( bc_customer[date_of_last_purchase], dateToday, DAY )

VAR res =

IF (

bc_customer[date_of_last_purchase],

SWITCH (

TRUE (),

daysDiff <= 30, "R5",

daysDiff <= 60, "R4",

daysDiff <= 120, "R3",

daysDiff <= 180, "R2",

daysDiff <= 360, "R1",

"R0"

),

"R0"

)

RETURN

res

where
date_of_last_purchase = MAX(bc_order[order_created_date_time])

I can visualize this in a table as follows

But I can't view this in a pie chart (see below) such that the pie is divided into the different recency bins which is what I want. In the pie chart, I am unable to drag the recency measure to the Values.

Maybe I would need to rewrite this measure as a new column? How would I do this? Or is it something else?

[v-eqin-msft]

Hi @rgjiang ,

Could you please share me with your pbix file after removing sensitive data?

Best Regards,
Eyelyn Qin

[rgjiang]

Hi Evelyn,

thanks for offering your help!

I am unsure how I would do that because I dont't have permission to import the data, and importing the data would also defeat the purpose of it using DirectQuery anyway? Hence the behavior would be different. DirectQuery on the other hand requires an access using a private key. Or do you mean that I should create a static and modified duplicate?

[AlexisOlson]

In order to use it as a category in the visual, it does need to be a calculated column.

Since it's a calculated column, you can rely on row context rather than needing to take a MAX.

recency =
VAR daysDiff = DATEDIFF ( bc_order[order_created_date_time], TODAY (), DAY )
VAR res =
    SWITCH (
        TRUE (),
        daysDiff <= 30, "R5",
        daysDiff <= 60, "R4",
        daysDiff <= 120, "R3",
        daysDiff <= 180, "R2",
        daysDiff <= 360, "R1",
        "R0"
    )
RETURN
    res

[rgjiang]

Hi @AlexisOlson,

thanks so much for your answer. Unfortunately, it says

OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.

Should I rather split this expression up or is there a simpler way? Sorry if these questions are dumb, I am still very much at the beginning of learning Power BI.

[AlexisOlson]

It's not a dumb question, different data sources have different DirectQuery limitations. I tested it with a DirectQuery to a Sql Server and it worked but BigQuery is apparently different.

Try each of these to see what the breaking factor is:

TodayFunction = TODAY ()

DateDifference = DATEDIFF ( DATE ( 2021, 1, 1 ), DATE ( 2021, 10, 29 ), DAY )

SwitchFunction =
    SWITCH (
        TRUE (),
        bc_order[order_created_date_time] < DATE ( 2020, 1, 1 ), "Prev Years",
        bc_order[order_created_date_time] < DATE ( 2021, 1, 1 ), "2020",
        "2021"
    )

[rgjiang]

Good to know!

I tried them all out and added the SwitchFunction as a new column. It turns out that this causes tis error

OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [Microsoft][BigQuery](70) Invalid query: No matching signature for operator < for argument types: DATETIME, TIMESTAMP. Supported signature: ANY < ANY at [9:55].

 If I understand this error correctly, this indicates that the data type of bc_order[order_created_date_time] in BigQuery needs to have type TIMESTAMP. Can I avoid this error without modifying the data in BigQuery because I have no write permissions?

[AlexisOlson]

I'm not quite sure but it sounds like the issue might be comparing date versus datetime.

Do you get the same error if you use TODAY() or DATEVALUE ( "1/1/2020" ) instead of DATE ( 2020, 1, 1 ) in the SWITCH?

[rgjiang]

Yes, unfortunately.