topic Re: Complex Dax Measure - Help Needed in DAX Commands and Tips

Complex Dax Measure - Help Needed

sqlguru448 — Thu, 03 Sep 2020 20:34:09 GMT

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

Re: Complex Dax Measure - Help Needed

AntrikshSharma — Sat, 29 Aug 2020 06:53:40 GMT

sqlguru448 I believe this article can help you, give it a try: https://www.daxpatterns.com/new-and-returning-customers/

Re: Complex Dax Measure - Help Needed

daxer-almighty — Sat, 29 Aug 2020 17:08:12 GMT

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.

Re: Complex Dax Measure - Help Needed

CNENFRNL — Sat, 29 Aug 2020 17:56:12 GMT

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 _summary

or 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

Re: Complex Dax Measure - Help Needed

sqlguru448 — Sun, 30 Aug 2020 22:28:40 GMT

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.

Re: Complex Dax Measure - Help Needed

CNENFRNL — Mon, 31 Aug 2020 00:12:22 GMT

sqlguru448

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.

Re: Complex Dax Measure - Help Needed

daxer-almighty — Mon, 31 Aug 2020 10:02:45 GMT

// 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

Re: Complex Dax Measure - Help Needed

daxer-almighty — Mon, 31 Aug 2020 10:07:20 GMT

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...

Re: Complex Dax Measure - Help Needed

sqlguru448 — Tue, 01 Sep 2020 22:16:47 GMT

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.

Re: Complex Dax Measure - Help Needed

Anonymous — Wed, 02 Sep 2020 10:03:55 GMT

SELECTCOLUMNS works in Power BI. Where are you implementing this measure?

https://dax.guide/selectcolumns/

Re: Complex Dax Measure - Help Needed

sqlguru448 — Wed, 02 Sep 2020 21:49:13 GMT

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

Re: Complex Dax Measure - Help Needed

Anonymous — Thu, 03 Sep 2020 08:41:57 GMT

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.

Re: Complex Dax Measure - Help Needed

Anonymous — Thu, 03 Sep 2020 08:48:33 GMT

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.

Re: Complex Dax Measure - Help Needed

sqlguru448 — Thu, 03 Sep 2020 20:35:15 GMT

Anonymous apologies for incorrect statements, instead of same products it should be specific products. I have corrected my initial post.

Re: Complex Dax Measure - Help Needed

Anonymous — Fri, 04 Sep 2020 10:44:08 GMT

sqlguru448

Still, your formulation of the problem does not let anyone solve this. When you say that some products are to be considered with some other products, you have to state how one product is connected to another. If you have groups of products that should trigger the logic, then you have to tell us exactly what the association is.

Please re-think how you'd like to phrase the problem because right now it's not possible to give you any answer.