Comparing two unrelated date tables with dax.

[Javimc]

Hi!

I have two unrelated tables with date and time.

Detection: ID, Date

Exits: Date, direction

First table shows the alarm id and the date, second table shows the the ins and outs from a building and the time of those ins and outs.

I need to know how many exits dates are contained in the detection date with a possible deviation of 10 seconds.

Is it possible?

[v-rongtiep-msft · Accepted solution]

Hi @Javimc ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 measures.

Measure =
VAR _1 =
    MAX ( Detection[Date] ) + TIME ( 0, 0, 10 )
VAR _2 =
    MAX ( Detection[Date] ) - TIME ( 0, 0, 10 )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), ( 'Table'[dATE] ) <= _1 && ( 'Table'[dATE] ) >= _2 )
    )

Measure2 =
VAR _1 =
    SUMMARIZE ( Detection, Detection[Date], "aaa", [Measure] )
RETURN
    IF ( HASONEVALUE ( Detection[Date] ), [Measure], SUMX ( _1, [aaa] ) )
//COUNTAX(FILTER(ALL(Detection),[Measure]<>BLANK()),[Measure])

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Javimc]

This was not exactly what i was looking for but pointed me in the right direction and really helped me.

Thanks for your help!