Filter function does not work properly, not filtering (DAX)

Question

I have a problem with my filter function.

I want my code to calculate a column that states whether the row is a returning customer for the therapist - so there has to be an earlier profile id (employment number), an earlier customer id, an earlier booking date, and the order status must be active. These things means that it's a returning customer and that it's a returning customer to the specific therapist (profile id)

ReturningCforT = IF(COUNTROWS(
FILTER(ALL(orders),
    orders[Column1.Profile_ID] =EARLIER(orders[Column1.Profile_ID]) &&
    orders[Column1.Customer_ID] = EARLIER(orders[Column1.Customer_ID]) &&
    orders[Column1.OrderStatus] = "Active" &&
    orders[Column1.BookingDate] > EARLIER(orders[Column1.BookingDate])
    
)) >= 1, "Yes", "No")

Currently I can see in my newly created column that there is a "Yes", but the orderstatus is not active on this line, which means that the orderstatus = Active filter, is not currently working.

I have tried to replace all function with value function, and I get the same result.

Can anyone help me solve this? Thank you.

Alexis Olson Alexis Olson · Accepted Answer · 2020-09-01T13:47:53

Your filter is looking at the entire table and checking each of the conditions for each row. Since there is a row that satisfies all of them, it returns "Yes" even though that row is not the current row.

I think you intended to apply that condition to the current row, not any row, so I'd suggest moving that outside the FILTER.

ReturningCforT =
IF (
    orders[Column1.OrderStatus] = "Active" &&
    COUNTROWS (
        FILTER (
            ALL ( orders ),
            orders[Column1.Profile_ID] = EARLIER ( orders[Column1.Profile_ID] )
                && orders[Column1.Customer_ID] = EARLIER ( orders[Column1.Customer_ID] )
                && orders[Column1.BookingDate] > EARLIER ( orders[Column1.BookingDate] )
        )
    ) >= 1,
    "Yes",
    "No"
)

Filter function does not work properly, not filtering (DAX)

1 Answers