Complex Dax Measure - Help Needed

[sqlguru448]

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

[sqlguru448]

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.

[CNENFRNL]

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

[daxer-almighty]

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

[sqlguru448]

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

[Anonymous]

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.

[Anonymous]

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

https://dax.guide/selectcolumns/

[sqlguru448]

@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

[Anonymous]

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

[sqlguru448]

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

[Anonymous]

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

[daxer-almighty]

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]

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.

[AntrikshSharma]

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

[CNENFRNL]

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