cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Determining how long a column equals a certain value (measure)

My goal is to be able to show "code 1 ran for this long and had a total downtime of however many minutes" and same for code 2.

I have a column that is holds either a 1 or a 2 representing the different codes I'm looking at. I also have a datetime column that increases by a minute since that is how often data it coming in. What would be the best way to go about doing this? I eventually plan to have this in a measure so when the user clicks a shift, date, and which code they want to look at, it will display on a card how long it ran and what the downtime is... I already have a downtime measure that is based off of a downtime column, so I just need to figure out how to get the amount of time the code ran for. Any ideas?

1 ACCEPTED SOLUTION
Resolver I

What I ended up doing since each row is one minute, is makking a column where it is always equal to 1. I then did a measure like this:

VAR TotalMinutes = CALCULATE(
SUM(vwSorterView[Duration As Int]),
FILTER(
vwSorterView,
vwSorterView[Data2] = 1
)
)
RETURN
IF(
TotalMinutes >= 1440, // If total minutes exceed 1440, show duration in days
INT(TotalMinutes/1440) & " days " & INT(MOD(TotalMinutes,1440)/60) & " hours " & RIGHT("0" & MOD(MOD(TotalMinutes,1440),60),2) & " minutes",
IF(
TotalMinutes >= 60, // If total minutes exceed 60, show duration in hours and minutes
INT(TotalMinutes/60) & " hours " & RIGHT("0" & MOD(TotalMinutes,60),2) & " minutes",
"00:" & RIGHT("0" & TotalMinutes,2) // Otherwise, show duration in minutes only
)
)

This was the only work around I could think of. If anyone else has a better way, please let me know!
Resolver I

What I ended up doing since each row is one minute, is makking a column where it is always equal to 1. I then did a measure like this:

VAR TotalMinutes = CALCULATE(
SUM(vwSorterView[Duration As Int]),
FILTER(
vwSorterView,
vwSorterView[Data2] = 1
)
)
RETURN
IF(
TotalMinutes >= 1440, // If total minutes exceed 1440, show duration in days
INT(TotalMinutes/1440) & " days " & INT(MOD(TotalMinutes,1440)/60) & " hours " & RIGHT("0" & MOD(MOD(TotalMinutes,1440),60),2) & " minutes",
IF(
TotalMinutes >= 60, // If total minutes exceed 60, show duration in hours and minutes
INT(TotalMinutes/60) & " hours " & RIGHT("0" & MOD(TotalMinutes,60),2) & " minutes",
"00:" & RIGHT("0" & TotalMinutes,2) // Otherwise, show duration in minutes only
)
)

This was the only work around I could think of. If anyone else has a better way, please let me know!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.