CODEWITHSUNDEEP
powerbi
user16116406
user16116406

Reputation: 3

Counting records under multiple criteria in DAX

I have 2 tables.

A. Customer table. Key column include 1. Customer ID (primary key) and 2. First Date purchase (in Date field) and 3. First purchase Date Year & Month (in text, like Jul 01).

enter image description here

B. Sales table. Key column include 1. Customer ID (foreign key) and 2. Sales order date (in Date field).

enter image description here

Both tables was in 1-many relationship. I want to find out how many customer return within 60 days since their first purchase, group by First purchase Date Year & Month. For example,

I will then perform calculation in pivot table for % of return customer per first purchase month, like below.

My desire result was a pivot table in below: enter image description here

I can do it by adding a calculated column in Customer table with below dax as filter to either 1 (the customer had reorder within 60days) or 0, then calculate at pivot table field:

IF(CALCULATE(COUNT(Sales[ProductKey]),FILTER(Sales,Sales[OrderDate]>Customers[DateFirstPurchase] && Sales[OrderDate] - Customers[DateFirstPurchase] <=60 && Sales[CustomerKey] = Customers[CustomerKey]))>0,1,0)

But as Sales had quite a large volumn, and it's not a best practice to use Filter in this case. Therefore, can this be achieve by either:

  1. Not using Filter function in calculated column ? i.e. check matching record from Customer table ? or other ways ? Or, ideally
  2. Use Dax instead of adding calculated column.

It's highly appreciated for yours' help. Thanks.

Upvotes: 0

Views: 1738

Answers (1)

msta42a
msta42a

Reputation: 3741

If you have a relationship in a model then you don't need a Sales[CustomerKey] = Customers[CustomerKey]; Try to use this calculation (not tested).

MeasureToCount = CALCULATE (
    COUNTROWS (
        CALCULATETABLE (
             VALUES(Sales[CustomerKey]),
            FILTER (
                Sales,
                 Sales[SalesOrderDate]  > RELATED(Customer[FirstPurchase])
                    &&  (Sales[SalesOrderDate])  <= ( RELATED(Customer[FirstPurchase]) + 60 )
            )
        )
    )
)

Now if you add "Year & Month" to Table visualization with measure, that should work.

Output from powerbi Desktop: enter image description here enter image description here

Upvotes: 0

Related Questions