Forum Discussion
sqlguru448
Helper III
6 years agoComplex 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 ...
daxer-almighty
Solution Sage
6 years ago// 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
__customersSurvivedCountdaxer-almighty
Solution Sage
6 years agoFrom 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...