Problems with MTBF calculations

[rbartlejr]

I've been trying to emulate the MTBF calculations in this blog post: https://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339.... I'm having problems with the Dax formulas in it. For example, the author shows:

Uptime =

VAR next = MINX(FILTER(Repairs,

                       Repairs[MachineName]=EARLIER(Repairs[MachineName]) &&

                       Repairs[RepairStarted]>EARLIER(Repairs[RepairStarted]) &&

                       Repairs[RepairType]<>"PM"

               ),Repairs[RepairStarted])

RETURN IF([RepairType]="PM", 0,IF(ISBLANK(next),

               DATEDIFF([RepairCompleted],NOW(),SECOND),

               DATEDIFF([RepairCompleted],next,SECOND)

           )

       )

I modified it to my data and get this:

Uptime =

VAR next = MINX(FILTER(Repairs,

          Repairs[Asset #]=EARLIER(Repairs[Asset #]) &&

          Repairs[DateIn]>EARLIER(Repairs[DateIn]) &&

          Repairs[comments]<>"Return to bin"

          ),Repairs[DateIn].[Day])

RETURN IF(Repairs[comments]="Return to bin", 0,IF(ISBLANK(next),

          DATEDIFF([DateOut],NOW(),DAY),

           DATEDIFF([DateOut],next,Day)

     )

)

I get this error: The syntax for ',' is incorrect. (DAX(VAR next = MINX(FILTER(Repairs, Repairs[Asset #]=EARLIER(Repairs[Asset #]) && Repairs[DateIn]>EARLIER(Repairs[DateIn]) && Repairs[comments]<>"Return to bin" ),Repairs[DateIn].[Day])RETURN IF(Repairs[comments="Return to bin", 0,IF(ISBLANK(next), DATEDIFF([DateOut],NOW(),DAY), DATEDIFF([DateOut],next,Day) ) ))).

I also get red  underlines under all of the columns next to EARLIER and DATEDIFF. Anyone have any ideas?

[Greg_Deckler · Accepted solution]

Looks like you are missing a comma or semi-colon after the VAR next line.

[Greg_Deckler]

Sounds like you need to change the commas to semi-colons ( ; ). Regional language settings.

[rbartlejr]

@Greg_Deckler Ok, I tried that and get this error: EARLIER/EARLIEST refers to an earlier row context which doesn't exist. I looked in the data and there are more than one asset #s in several rows.

[Greg_Deckler]

And is this a column you are creating? It should be a column and not a measure if I remember correctly.

[rbartlejr]

It shows in the blog article as a measure; the data is set into columns for date, asset #, etc. I can calculate a column for the repair time (already done) since the Repair Hours measure didn't work either.

[Greg_Deckler]

Here is the original PBIX attached below if it helps.

I checked and Uptime is a calculated column. Also, read through the comments as I think there were some bugs/issues that were hammered out that I eventually included in my book DAX Cookbook. The book now has the best version of this. I can take a look at that and see if it helps. Let me know. 

[rbartlejr]

Spoiler

I appreciate the PBIX file. You were right but it still doesn't work. Here is a screenshot:

[Greg_Deckler]

Not to be that guy, but the code in the PBIX file I sent certainly works.

[Greg_Deckler · Accepted solution]

Looks like you are missing a comma or semi-colon after the VAR next line.

[rbartlejr · Accepted solution]

Yes! I was missing the comma. BTW I just bought your book. Thanks for your help!