Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

PowerBI Desktop

Hello PowerBI community      @Amit, @Greg , @tamerj1 , @lbendlin,   @amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler    @christinepayton @audreygerred    @LukeB,...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hello lbendlin ,

 

Sorry about the delayed response. I was caught up on some other urgent things. But here is the sample dataset. 

I will more than appreciate any of your help. 

lbendlin's avatar
lbendlin
Super User
1 year ago

 

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY5LCoAwDESvIlkLSWrqZ+nvFKX3v4YdqlUR6S5vHjMkBJqpJXEsyk60B3QFYhtoQeRZDZEAhgLwK6IpV5wlUGH1GeC33L996l8AvyOyp7dX//ufVf4bK/8p57v52feV/XMM+/EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equipment = _t, CommissionedDate = _t, BreakdownDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CommissionedDate", type date}, {"BreakdownDate", type date}}),
    process = (tbl)=>
    let
    #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 then null else [BreakdownDate]-#"Added Index"[BreakdownDate]{[Index]-1}),
    #"Grouped Rows1" = Table.Group(Table.SelectRows(#"Added Custom", each ([Custom] <> null)), {"Equipment"}, {{"Avg", each List.Average([Custom]), type duration}})
in
    if Table.RowCount(tbl)=1 then null else #"Grouped Rows1"[Avg]{0},
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Equipment", "CommissionedDate"}, {{"Rows", each _, type table [BreakdownDate=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "MTBF", each process([Rows]),type duration),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"})
in 
    #"Removed Columns"

 

 

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello lbendlin

     

    Can you please suggest this formula in PowerBI DAX instead of M-Query? 

    I don't understand M-Query that much. Plus, its a very hard programming language. 

     

    Thank you!

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hello lbendlin ,

         

        I am not sure how are you getting MTBF for Equipment A as 30.5 days and Equipment B as 24.50 days. Because, Equipment A has failed 3 times and the total operating time is 30+61+91 = 182 days, No of times it has failed = 3, So, MTBF should be 60.67 for Equipment A. 

         

        Similarly, For Equipment B, the total operating time is 62+93+111 = 266 days and it also failed 3 times, so the MTBF for Equipment B should be 88.67 days. 

         

        Can you please tell me your logic here? 

         

        Thanks.