Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi All,
I have a table of data built from a folder of CSV files containing time series data - the data can be per second or per 5 minutes.
Each row has a timestamp and several columns of data.
I am trying to find the total amount of time a value spent below X. This would be much simpler if the logged data was per second, but as it can be per 5 mins as well it makes it slightly harder.
I guess I need to find the start timestamp the value drops below the threshold and the timestamp it rises above, find the datediff between the star and finish, then add up all the occurrences throughout the data.
Taking the data table below, I want to return the total time the Output was logged below 400, the answer to the below would be 10 mins 2 seconds:
Timestamp | Output | Cross Flow |
09/09/2022 10.00.01 | 500 | 45 |
09/09/2022 10.00.02 | 375 | 60 |
09/09/2022 10.00.03 | 515 | 40 |
09/09/2022 10.00.04 | 500 | 45 |
09/09/2022 10.00.05 | 350 | 65 |
09/09/2022 10.00.06 | 450 | 50 |
09/09/2022 10.05.06 | 300 | 70 |
09/09/2022 10.10.06 | 350 | 65 |
09/09/2022 10.15.07 | 515 | 40 |
Solved! Go to Solution.
oooh, Nyquist and Shannon will not be happy about this...
Anyway, do you need the solution in DAX or in Power Query? In DAX it would look something like
Measure_t =
VAR a =
ADDCOLUMNS(
SELECTCOLUMNS(
'Table',
"Timestamp", 'Table'[Timestamp],
"Output", 'Table'[Output]
),
"PT",
VAR t = [Timestamp]
RETURN
MAXX( FILTER( 'Table', 'Table'[Timestamp] < t ), 'Table'[Timestamp] )
)
VAR b =
ADDCOLUMNS(
a,
"PV",
VAR p = [PT]
RETURN
MAXX( FILTER( 'Table', 'Table'[Timestamp] = p ), 'Table'[Output] )
)
RETURN
SUMX( FILTER( b, COALESCE( [PV], 400 ) < 400 ), [Timestamp] - [PT] )
By the way the actual result is 10 minutes 3 seconds (not 10 minutes 2 seconds)
oooh, Nyquist and Shannon will not be happy about this...
Anyway, do you need the solution in DAX or in Power Query? In DAX it would look something like
Measure_t =
VAR a =
ADDCOLUMNS(
SELECTCOLUMNS(
'Table',
"Timestamp", 'Table'[Timestamp],
"Output", 'Table'[Output]
),
"PT",
VAR t = [Timestamp]
RETURN
MAXX( FILTER( 'Table', 'Table'[Timestamp] < t ), 'Table'[Timestamp] )
)
VAR b =
ADDCOLUMNS(
a,
"PV",
VAR p = [PT]
RETURN
MAXX( FILTER( 'Table', 'Table'[Timestamp] = p ), 'Table'[Output] )
)
RETURN
SUMX( FILTER( b, COALESCE( [PV], 400 ) < 400 ), [Timestamp] - [PT] )
By the way the actual result is 10 minutes 3 seconds (not 10 minutes 2 seconds)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |