Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rgjiang
Frequent Visitor

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()
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

 

rgjiang_0-1635509201409.png

 

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.

 

rgjiang_1-1635509428501.png

 

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

8 REPLIES 8
v-eqin-msft
Community Support
Community Support

Hi @rgjiang ,

 

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

 

Best Regards,
Eyelyn Qin

 

 

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
Super User
Super User

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

 

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.

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"
    )

 

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?

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?

Yes, unfortunately.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.