Dear Experts
Please refer to this topic tutorial link: https://towardsdatascience.com/explore-the-potential-of-products-through-customers-purchase-behaviour-in-power-bi-basket-a1f77e8a2bf6
My question is regard the calculated column (IsFirstorder) as it said in the page :
" First, classify all orders for the sales table, in all orders of the customer, one or more orders with the earliest order date are classified as the first order, the rest are “non-first”:" , when I implemented it on my showcase it gives me (out of memory problem) this happen when I call the measure (CustPurchaseOthersSubcategoryAfter ) as on this image:
because the table I’m using it is above 10M rows not like the one used on the (Page tutorial) , Is there any alternatives such as :
1- Using custom column in Query editor?
Or
2-Using measure ,
or any other suggestion to avoid the out of memory and/or bad performance ?
If you are building columns and can do it in Power Query in most cases you should so the column can compress (if possible). Why can’t you use a measure?
Hi, @MAAbdullah47 !
Here a short example of the way, how you could calculate sales after first order date.
I’ve also attached PBIX for you, to be more clear. In sample model i also have product category, subcategory and customers (it is AdwentureWorks sample).
What i’ve done:
- Calculate dynamic FirstSalesDate (which is changed in every Month / Day of Calendar) using MINX and Summarize (measure [01 First purchase date (dynamic)])
01 First purchase date (dynamic) =
MINX(
– gives us opportunity to build virtual table we needed
SUMMARIZE(
– creates virtual table of required detalisation
‘Sales’,
‘Customer’[CustomerKey],
– we’d like to drillthrough Customers and Product
‘Product’[ProductKey],
– and we add date to be calculated
‘Date’[Date]
),
– as min from virtual table
‘Date’[Date]
)
- Next i use Calculate and ALL to change from “dynamic” to “absolute” - so i can see i every selected period First Sales Date (measure called [02 02 First purchase date (absolute)])
02 First purchase date (absolute) =
– use Calculate to add filter to measure
– ALL clears all date filters
CALCULATE([01 First purchase date (dynamic)],ALL(‘Date’))
-
We need to have Start date for calculations after first sales date
So here simply i use DATE function and add +1 day to date
CalcSalesStartDate =
IF ([03 Revenue]<=0,BLANK(),
DATE(YEAR([02 First purchase date (absolute)]),MONTH([02 First purchase date (absolute)]),DAY([02 First purchase date (absolute)])+1)
)
-
And we need to calculate end date - here i use TODAY () or you could use any other date variance you’d like to have
-
And finally we go to calculate measure called [04 Revenue after first order]
where i used CALCULATE and DATESBETWEEN to setup revenue calculations from StartDate till EndDate
04 Revenue after first order =
CALCULATE(
[03 Revenue],
DATESBETWEEN(‘Date’[Date],[CalcSalesStartDate],[CalcSalesEndDate])
)
Also, you could see some other scenarios regarding your questions: First Purchase Date - DAX
Hope, it helps you!
BR,
Oleg
SalesAfterFirstOrderDate.pbix (7.8 MB)
Thank you @Oleg.Poddubnyy for your help , I’ll review and get back to you.
Ok @Oleg.Poddubnyy
Can you help me to put the measure that fits with the (below labeled red matrix) from your pbix file ?
I mean what sort of editing I do in the measure (CustPurchaseOthersSubcategoryAfter) to be fit with your suggested measure?
Hello, @MAAbdullah47 !
What i’ve add to calculate distinct customers q-ty for other subcategories after initial subcategory first sales (or purchase) date, based on basket analysis logik.
- Add table to compare by products - see in attached file And Product table. And setup Inactive relationship in model to sales table
- Calculate distinct value for purchased customers - called “customers”
- Calculate internal measure # Customers Both (Internal)
- And finally calculate desired measure 05 Customers after first order. It uses same logik as CustPurchaseOthersSubcategoryAfter
You could find this matrix on page 2 of attached file.
Hope, it helps you!
BR.
Oleg
SalesAfterFirstOrderDate.pbix (7.8 MB)
Thank you @Oleg.Poddubnyy appreciate your help , I’ll c and get back to you.
Thank you @Oleg.Poddubnyy , I’m focusing not the (Intersect) , I need the second on (Below table) on the pbix of the (Tutorial link) :
I’m not sure if I can use the same logic on (05 Customers after first order ) Instead of Intersect I should use (Except) , Is my guess right?
Note:
I mean I need to do editing of the Measure (CustPurchaseOthersSubcategoryAfter )
Thanks for update, @MAAbdullah47 !
Could you kindly share PBI where you’re working? I’ll try to help with CustPurchaseOthersSubcategoryAfter measure editing with this row: ‘Sales’[IsFirstOrder] = FALSE
Hi @Oleg.Poddubnyy
Attached , Just to make it more clear please check the screen-shot:
Basket Analysis 2.0.pbix (1.2 MB)
My goal is to avoid using calculated column because it consume memory if the data is big and the dashboard will be freezing if calculated column used.
Hi. @MAAbdullah47
Numbers are the same with measure instead calculated column 
What was done:
- calculate measure FirstOrderDate which gives first order date to all orders inside single customer
- calculate measure FirstPurchase which returns 1 if order is the first by order date inside single customer, or 0 in other cases
so we get such table to be clear
- Update measure CustDistinctValue as it uses ‘Sales’[IsFirstOrder] - new version called CustDistinctValue v.1
- Update measure CustPurchaseOthersSubcategoryAfter - new version called CustPurchaseOthersSubcategoryAfter v.1
and here is the final result
PBIX file in attach
BR,
Oleg
Basket Analysis 2.0 _ Customer Both after First Purchase.pbix (1.2 MB)
1 Like
Thank you so much @Oleg.Poddubnyy , I’ll check it on my showcase and get back to you , but from the first quick check I think it is by 95% same as what I’m targeting , so Just give me one day test.
Hello @MAAbdullah47 , just following up if you were able to check on your showcase?
I’ll do my friend just give me for today.
