Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Hi @Norbertus ,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |