Forum Discussion
Complex Dax Measure - Help Needed
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
14 Replies
- AntrikshSharmaCommunity Champion
sqlguru448 I believe this article can help you, give it a try: https://www.daxpatterns.com/new-and-returning-customers/
- CNENFRNLCommunity Champion
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 _summaryor 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
- daxer-almightySolution Sage
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. - sqlguru448Helper III
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.
- CNENFRNLCommunity Champion
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, productAs I mentioned, the calculated table is created for more clarity.
- daxer-almightySolution Sage
// 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