Forum Discussion
sqlguru448
6 years agoHelper III
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 ...
sqlguru448
6 years agoHelper 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.
- CNENFRNL6 years agoCommunity 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-almighty6 years agoSolution 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- daxer-almighty6 years agoSolution SageFrom the above code it's rather easy to create a measure that will give you the sales amount for the customers you're interested in...