Forum Discussion

rgjiang's avatar
rgjiang
Frequent Visitor
4 years ago

Purchase recency with DirectQuery

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() ...
Need Help
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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's avatar
rgjiang
Frequent Visitor
4 years ago

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's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      rgjiang
      Frequent Visitor
      4 years ago

      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's avatar
        AlexisOlson
        Super User
        4 years ago

        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?