Combine Error Messages when present in 1 timebox

[Anonymous]

Hi,

We have data in our dataset (logfile) that are the erroritems from an object

Date Time	Object	Description	Count		Time	Hour	Minute
4-10-2022 06:00	A	Overflow	1		06:00	06	00
4-10-2022 06:15	A	Overflow	1		06:15	06	15
4-10-2022 08:43	A	Overflow	1		08:43	08	43
4-10-2022 08:59	B	Out of Order	1		08:59	08	59
4-10-2022 09:15	B	Test	1		09:15	09	15
4-10-2022 09:23	B	Out of Order	1		09:23	09	23

We have a variable (CombineHours). In this example 1 hour

There is also a dimension table with hours & minutes

The result we need is dat if there are 2 error logs (from the same object and the same description) in 1 hour (variable) that has count as 1 logitem.

Date Time	Object	Description	Count
4-10-2022 06:00	A	Overflow	1
4-10-2022 06:15	A	Overflow	0
4-10-2022 08:43	A	Overflow	1
4-10-2022 08:59	B	Out of Order	1
4-10-2022 09:15	B	Test	1
4-10-2022 09:23	B	Out of Order	0

hopefully someone can help me 

[Anonymous · Accepted solution]

Hi,

after a few hours thinking I build the next solution:

Measure: =
VAR offset = 0
VAR __hour =
    SELECTEDVALUE ( BundelenUren[Uren], MAX ( BundelenUren[Uren] ) )
VAR timetable =
    ADDCOLUMNS (
        GENERATESERIES ( 0 + offset, 24, __hour ),
        "start", TIME ( [Value], 0, 0 ),
        "end", TIME ( [Value] + __hour, 0, 0 )
    )
VAR result =
    SUMMARIZE (
        timetable,
        [start],
        [end],
        "aantal",
            CALCULATE (
                COUNTROWS (
                    GROUPBY (
                        'fact-abb-melding',
                        'fact-abb-melding'[Datum],
                        'fact-abb-melding'[Asset],
                        'fact-abb-melding'[Bericht]
                    )
                ),
                FILTER (
                    'fact-abb-melding',
                    TIMEVALUE ( 'fact-abb-melding'[EventDateTimeUTC] ) >= [start]
                        && TIMEVALUE ( 'fact-abb-melding'[EventDateTimeUTC] ) < [end]
                        && 'fact-abb-melding'[InOut] = "+"
                )
            )
    )
VAR timeboxaantal =
    SUMX ( result, [aantal] )
RETURN
    timeboxaantal

The only thing is that the measure is verry slow because i have 5 mio records

[Anonymous]

Hi @Anonymous ,

Please try:

Column = 
var pretime = CALCULATE(MAX('Table'[Date Time]),FILTER(ALLEXCEPT('Table','Table'[Object],'Table'[Description]),'Table'[Date Time]<EARLIER('Table'[Date Time])))
var diff = DATEDIFF(pretime,'Table'[Date Time],MINUTE)
return
IF(diff=BLANK()||diff>60,1,0)

Best Regards,

Jay