Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Combine Error Messages when present in 1 timebox

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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