Need help with metric in direct query mode

[VedranR]

Hi everyone,

I need help with some issue in PBI.

In Power BI I have two tables 'Calendar' and 'report dealer_count_event_view'.
In the 'Calendar' table I have the data Date, while in the 'report dealer_count_event_view' table (which I import via direct query) we record all status changes (event_type) by users and I have separate data only for the date (event_date) and for the date and time together (event_time) for each record.

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

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

I have a metric that works when the 'report dealer_count_event_view' table is not in direct query mode (ther is error fatching problem), but works when data is imported.

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

This is what the 'report dealer_count_event_view' dataset looks like:

user_id	event_time	event_date	event_type
823	25.12.2024 08:41	25.dec.24	deactivated
823	22.1.2025 16:11	22.jan.25	reactivated
823	23.1.2025 17:25	23.jan.25	deactivated
823	19.2.2025 15:51	19.feb.25	reactivated
17525	16.2.2025 16:35	16.feb.25	reactivated
19670	20.1.2025 19:21	20.jan.25	deactivated
192507	16.12.2024 13:20	16.dec.24	deactivated
192507	13.1.2025 15:11	13.jan.25	reactivated
192507	15.1.2025 15:12	15.jan.25	deactivated
535114	17.11.2024 09:12	17.nov.24	deactivated
535114	17.11.2024 08:36	17.nov.24	reactivated
535114	21.2.2025 19:33	21.feb.25	reactivated
535114	22.2.2025 19:25	22.feb.25	deactivated
676192	21.2.2025 15:41	21.feb.25	deactivated
676192	24.2.2025 11:25	24.feb.25	reactivated

Thanks in advance!

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @mark_endicott  offered, and i want to offer some more information for user to refer to.

hello @VedranR , you can refer to the following solution

The sample data is the same as you provided, create the following measures.

MEASURE =
VAR a =
    FILTER (
        ALLSELECTED ( 'report dealer_count_event_view' ),
        [user_id] IN VALUES ( 'report dealer_count_event_view'[user_id] )
    )
VAR b =
    MAXX ( a, [event_time] )
RETURN
    MAXX ( FILTER ( a, [event_time] = b ), [event_type] )

Measure2 =
VAR a =
    FILTER (
        ALLSELECTED ( 'report dealer_count_event_view' ),
        [user_id] IN VALUES ( 'report dealer_count_event_view'[user_id] )
    )
VAR b =
    MAXX ( a, [event_time] )
RETURN
    MAXX (
        TOPN ( 1, FILTER ( a, [event_time] < b ), [event_time], DESC ),
        [event_type]
    )

Measure3 =
CALCULATE (
    DISTINCTCOUNT ( 'report dealer_count_event_view'[user_id] ),
    FILTER (
        'report dealer_count_event_view',
        [Measure 2] <> "deactivated"
            && [Measure] = "deactivated"
    )
)

OUTPUT

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mark_endicott]

@VedranR - If your DAX works in Import mode, then you should be asking the question why this needs to be Direct Query. 

Performance will always be worse, and whilst end users say they need 'live' data, but are they really sat there watching your report refresh 24/7 - unlikely. 

If you would like someone to help, you'll need to supply your DAX.