Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Binary Runtime/Downtime Visualization

I have a data set similar to the table below with a machine ID, and columns for start and end of downtime periods. Machines may or may not have multiple records for downtime intervals. I'd like to be able to have a binary visual similar to the below paint mockup showing date along x axis and the status of the machine as either "up/running" or "down" . I figure a calendar table is in order but struggling with how to transform/join the data to get the desired result for visualization - essentially if a given date is between a start/end date for a given machine it's a 0 (down) and if it's not it's a 1 (up) for that machine.

Tried searching the forums but haven't found quite the result I'm looking for, please let me know if I missed a solution (first time posting on here)

MachineIDDowntimeStartDowntimeEnd
11-May1-Jul
11-Jan1-Feb
21-Jan1-Mar
21-Sep5-Sep
31-Apr4-Apr
48-May31-May
41-Mar5-Mar

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    Here is one way, Date from a connected Date Table, test measure as below

    test = 
VAR CurDate = MAX('Date'[Date])
VAR T1 = SELECTCOLUMNS(GENERATE('Table',DATESBETWEEN('Date'[Date],'Table'[DowntimeStart],'Table'[DowntimeEnd])),"Dates",[Date])
RETURN
IF(CurDate in T1, 0, 1)

     

  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    You can use power query to expand all dates from start date and end date:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MzsjMS/V0UdJRcskvzyvJzE0NLkksKkHiu+alKMXqRCsZAsUMdX0TK8G0V2kOkqhXYh6YdktNAosaoYj6JhYhiQanFgBpUzANEjUGizoWFAFpEzANEjUB8iygthlDrIUJQwwEGQE2OBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"MachineID", Int64.Type}, {"DowntimeStart", type date}, {"DowntimeEnd", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each {Number.From([DowntimeStart])..Number.From([DowntimeEnd])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type2"

    Then use the following measure:

     

    Measure = IF(MAX('Date'[Date]) IN VALUES('Table'[Custom]),0,1)

     

     

    For more details, please refer to the pbix file. 

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    Here is one way, Date from a connected Date Table, test measure as below

    test = 
VAR CurDate = MAX('Date'[Date])
VAR T1 = SELECTCOLUMNS(GENERATE('Table',DATESBETWEEN('Date'[Date],'Table'[DowntimeStart],'Table'[DowntimeEnd])),"Dates",[Date])
RETURN
IF(CurDate in T1, 0, 1)

     

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    You can use power query to expand all dates from start date and end date:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MzsjMS/V0UdJRcskvzyvJzE0NLkksKkHiu+alKMXqRCsZAsUMdX0TK8G0V2kOkqhXYh6YdktNAosaoYj6JhYhiQanFgBpUzANEjUGizoWFAFpEzANEjUB8iygthlDrIUJQwwEGQE2OBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"MachineID", Int64.Type}, {"DowntimeStart", type date}, {"DowntimeEnd", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each {Number.From([DowntimeStart])..Number.From([DowntimeEnd])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type2"

    Then use the following measure:

     

    Measure = IF(MAX('Date'[Date]) IN VALUES('Table'[Custom]),0,1)

     

     

    For more details, please refer to the pbix file. 

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai