Forum Discussion
Complex Dax Measure - Help Needed
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.
// 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- From 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...
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.
SELECTCOLUMNS works in Power BI. Where are you implementing this measure?
https://dax.guide/selectcolumns/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
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.
