Calculate alerts before an event

[Roberto]

Hi All,
I’m facing some issues on a DAX measure aimed to calculate the number of alerts on a specific device happening 3 hours before an incident on the same device.
I set up a simple model to make my try but it’s not working as expected.
The Inc1 at 9.00 AM on Appliance1 has 2 alerts happening the 3 hours before, while the measure counts 4.
Also, selecting the device on the dimension the numbers change again.
What am doing wrong?

Thanks for help

Roberto

Update: Setting up the relation with the Calendar now numbers are correct. I wonder how this measure will behave with millions of rows.

[alerts.pbix|attachment]

alerts.pbix (31.4 KB) Events.xlsx (16.8 KB)

[BrianJ]

@Roberto,

Let me know what you think of the approach below. I completely reworked your data model into a single fact table star schema and used the following M code to calculate minutes between the alert and the start of the incident:

#“Added Custom” = Table.AddColumn(#“Reordered Columns1”, “Minutes Between”, each Duration.TotalMinutes(Duration.From( [inc start] - [alert start] )))

The whole thing is now completely dynamic and most of the heavy lifting is done preload in Power Query (or better yet, SQL if you have that option), so it should perform quite well even with millions of rows.

I hope this is helpful. Full solution file below.

– Brian

eDNA Forum - alerts solution.pbix (80.2 KB)

[EnterpriseDNA]

Hi @Roberto , did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[Roberto]

Hi @BrianJ,
this approach worked just fine. I have to apply on real data that unfortunately is in CSV files, so I expect some hard time with PQ.

Thanks for your solution

Roberto

[Roberto]

Hi @BrianJ just as I feared the merging step on CSV of about 1M rows each, caused the PQ to last forever.
I’ll need to find a different way just using DAX

[Greg]

Hi @Roberto. Your DAX was very close … I’ve made a couple of adjustments and created a new DAX measure, and here’s my code:

alerts started 3 hrs before 2 =
VAR _CurrentDevice = MIN( incidents[device] ) 
VAR _PeriodEnd = MIN( incidents[start] ) 
VAR _PeriodStart = MIN( incidents[start] ) - TIME( 3, 0, 0 ) 
VAR _Result = CALCULATE(
    COUNTROWS( alerts ),
    FILTER(
        alerts,
        alerts[device] = _CurrentDevice &&
        alerts[start] >= _PeriodStart &&
        alerts[start] <= _PeriodEnd
    )
) 

RETURN
_Result

This gives me the following results:

Hope this helps when applied to your data volume.
Greg
eDNA Forum - Alerts before Events.pbix (31.7 KB)

[Roberto]

@Greg you made my day! It worked flawlessly and fast! Actually, the cardinality was above 15 billion (18K Incidents X 854K alerts), and taming PBI with these numbers is pretty hard. So I moved the calculation to the proper level and used some CROSSFILTER to reduce calculation effort.

Thanks a lot

Roberto

[Greg]

Glad it helped.
Greg