Forum Discussion
Complex Dax Measure - Help Needed
// 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.
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
- sqlguru448
Yeah, I understand but the description you gave at the beginning of the thread does not really correspond to the SQL. At the beginning you're talking about matching on specific products, not about a hazy match on some products.
Please think carefully about the phrasing of the problem and then post it here. The more info you'll include (maybe some pictures as well), the better chance that you'll get a good answer.
I do understand the SQL, of course, but I want to know how entities are mapped to your PBI model.
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.