Problem with DAX measure

[VedranR]

I need help with a problem in PBI.

In Power BI I have two tables "Calendar" and "report dealer_count_event_view".

In the table 'Calendar' I have the data Date, while in the table 'report dealer_count_event_view' (which I import via direct query) we record all status changes (event_type) by the user and I have separate data for only the date (event_date) and for the date and time together (event_time) for each record, as well as the data in which package which status occurred.

It can happen that a user has multiple records on the same day, but at different times.

I need to create a report that will count only users who have deactivated the last record in the observed month (which does not have to be on the last day of the month), but in the same month, on the day before that last record with deactivated status (event_type) there is a record with a status other than deactivated.

I have a metric that works, but in the report I cannot separate the results into packages from the last record in the month, the record that contains the deactivation data.

Is it possible to implement this in a single DAX metric in data query mode?

Thanks in advance

[DataNinja777]

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,