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
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?

      • rgjiang's avatar
        rgjiang
        Frequent Visitor
        4 years ago

        Yes, unfortunately.