Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Folks,
I have a complex requirement in Power BI reporting which is sourced from Azure Analysis Services Tabluar Model via Live Connection. I cannot change the Tabular model, so the measure or Dax needs to be implemented in Power BI report.
I have Sales Fact table and Customer, Product, Location Dimension table, the task is to pull Sales amount of only those customers who have ordered the specific Products from the same store/location within 90 days of Purchase date.
For example Customer A has Purchased Product 123 from location TX on 1/1/2020 with Amt $100
Customer B has Purchased Product 456 from Location CA on 2/1/2020 with Amt $10
Customer A has purchased Product 456 from Location TX on 2/28/2020 with Amt $100
Customer B has Purchased Product 456 from Location CA on 8/1/2020 with Amt $10
Customer C has purchased Product 789 from Location NY on 5/1/2020 with Amt $1000
so my report should pull only Customer A as he has purchased different product at same location and store and within 90 days of initial/previous purchase date.
Please help, any help is really appreciated.
Thanks
Thanks @AntrikshSharma, I will look into the article.
@daxer-almighty , it is a OR, if a customer purchases same product within 90 days during the "time frame" i.e. start and end date of purchase date.
@CNENFRNL , how would this DAX look at same customer, location and product? it is just looking at the interval. Pleasee explain.
Thank you.
Thanks to the powerful SUMMARIZECOLUMNS func, sales data can be summarized per dimensions like customer, location and product in one shot; similar to
Select customer, location, product, MIN(date), MAX(date)
From sales
Group By customer, location, product
As I mentioned, the calculated table is created for more clarity.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
// This will return all the customers
// visible in the current context who
// in the selected period of time in the same
// location bought at least(!) one and the same
// product at least 2 times and the purchases were
// made within at most 90 days within each
// other. This measure is fully responding
// to all filters, so please interpret it
// carefully WITHIN THE CURRENT CONTEXT.
// Transactions is the main fact table connected
// to dimensions: Customer, Location,
// Product, Date (joins to Transactions
// on [Purchase Date]) via the standard
// *:1 one-way relationship.
[# Cust] =
var __sections =
// partition the transactions
// by customer, product and
// location
SUMMARIZE(
Transactions,
Customer[CustID],
Product[ProdID],
Location[LocID]
)
// for each section, check if
// there are at least 2 transactions
// within at most 90 days of each
// other and if there are, include
// the section in the final set
var __qualifiedSections =
FILTER(
__sections,
CALCULATE(
// get all dates that are present
// in the transactions for the
// current section
var __sectionPurchaseDates =
SUMMARIZE(
Transactions,
Dates[Date]
)
var __datesIfNextDateWithin90Days =
FILTER(
__sectionPurchaseDates,
// calc the days to next purchase
var __currentDate = Dates[Date]
var __nextDate =
MAXX(
FILTER(
__sectionPurchaseDates,
Dates[Date] < __currentDate
),
Dates[Date]
)
var __daysBetweenDates =
__nextDate - __currentDate
return
__daysBetweenDates <= 90
&&
// this condition must be here
// since the next date is BLANK
// for the last date in question
__daysBetweenDates > 0
)
return
NOT ISEMPTY(
__datesIfNextDateWithin90Days
)
)
)
var __customersSurvivedCount =
DISTINCTCOUNT(
SELECTCOLUMNS(
__qualifiedSections,
"@CustID", Customer[CustID]
)
)
return
__customersSurvivedCount
@daxer-almighty Thank you so much for helping me out, I am unable to implement last section i.e. from "SelectColumns" my dax intellisense is not recognising it.
Hi
If you can't use SELECTCOLUMNS, replace the corresponding bit of code with this:
var __customersSurvivedCount =
calculate(
countrows( Customer[CustID] ),
__qualifiedSections,
ALL( Transactions )
)
My code returns the number of customers but you can easily change so that it returns the sum of transactions for the customers in question.
@Anonymous I am implementing the measure in Power BI desktop.
basically I am trying to mimic below SQL code from the view
-- get specifc product which starts with 'Toys%'
Select * into ##Toys From vwSales_Fact
where Product like 'Toys%'
-- get specifc Product which starts with 'stat%'
Select * into ##stat From vwSales_Fact
where Product like 'stat%'
Select R1.*, From ##Toys r1
inner join ##stat E1 on r1.customer_ID= E1.customer_ID and r1.Location_ID= E1.Location_ID
and r1.state= E1.state
And datediff(dd,r1.purchase_date,E1.purchase_date) <=90
@Anonymous apologies for incorrect statements, instead of same products it should be specific products. I have corrected my initial post.
Hi there.
@sqlguru448, the task at hand is underspecified. Please make it more clear.
You say "the task is to pull Sales amount of only those customers who have ordered the same Product from the same store/location within 90 days of Purchase date."
It seems that such a calculation should only be performed when 1) only one product is visible and 2) one store/location has been selected. Please confirm this is true and if not, please give rules for the calculation in case the above conditions are violated.
What happens when a customer bought product P on day D at store S, then the same product on day D + 20 at the same store and then the same product on day D + 200 at the same store? Is your condition an "OR" or "AND" condition? Should all the purchases be made within 90 days of some other or should there be at least one pair of purchases with this property?
Thanks.
@sqlguru448 I believe this article can help you, give it a try: https://www.daxpatterns.com/new-and-returning-customers/
Hi, there, pls refer to the following calculated table formula for details,
Debug =
VAR _summary =
SUMMARIZECOLUMNS (
dCust[Customer],
dLoc[Location],
dProd[Product],
fSales,
"Times", COUNTROWS(dDate),
"MinDate", MIN ( dDate[Date] ),
"MaxDate", MAX ( dDate[Date] ),
"Interval", INT ( MAX ( dDate[Date] ) - MIN ( dDate[Date] ) )
)
VAR _tb =
FILTER ( _summary, [Interval] > 0 && [Interval] <= 7 )
RETURN
_summary
or change the last line to turn it into a measure
RETURN
--_summary
CONCATENATEX(_tb, [Customer], UNICHAR(10))
it's based on a most idealized data model
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |