Problem with DAX measure

Hi VedranR ,

Yes, you can implement this in a single DAX measure while using DirectQuery mode, but it requires careful handling of filtering. The goal is to identify the last record per user in the observed month where the event type is "Deactivated" and ensure that the day before that last deactivation, the same user had a record with a status other than "Deactivated."

To achieve this, first, we need to determine the selected month by extracting the maximum date from the 'Calendar' table. Then, we find the last deactivation record for each user within that month. This can be done using SUMMARIZE to group users and their respective packages while calculating the last event date where the status was "Deactivated."

Measure_Deactivated_Count = 
VAR SelectedMonth = MAX('Calendar'[Date])
VAR LastDeactivationPerUser = 
    ADDCOLUMNS(
        SUMMARIZE(
            'report dealer_count_event_view',
            'report dealer_count_event_view'[user_id],
            'report dealer_count_event_view'[package] 
        ),
        "@LastDeactivationDate", 
            CALCULATE(
                MAX('report dealer_count_event_view'[event_date]),
                'report dealer_count_event_view'[event_type] = "Deactivated",
                MONTH('report dealer_count_event_view'[event_date]) = MONTH(SelectedMonth),
                YEAR('report dealer_count_event_view'[event_date]) = YEAR(SelectedMonth)
            )
    )
VAR ValidUsers =
    FILTER(
        LastDeactivationPerUser,
        CALCULATE(
            COUNTROWS('report dealer_count_event_view'),
            'report dealer_count_event_view'[event_date] = [@LastDeactivationDate] - 1,
            'report dealer_count_event_view'[event_type] <> "Deactivated"
        ) > 0
    )
RETURN
CALCULATE(
    DISTINCTCOUNT('report dealer_count_event_view'[user_id]),
    KEEPFILTERS(ValidUsers)
)

This measure works in DirectQuery mode and ensures that only users who have deactivated their last record in the observed month are counted, provided that on the previous day, they had a different status. Let me know if you need any refinements!

Best regards,