Forum Discussion

some1else's avatar
some1else
Helper II
3 years ago

This calculated column makes everything slow

I've followed this blog post to make a calculated column of my own, applied to a table of 3million rows. https://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/b...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

some1else It's going to be extremely difficult to troubleshoot this without some sample data as text and your measure formula.

some1else's avatar
some1else
Helper II
3 years ago

Sample data at the end. I want to look at just one specific code "A1".
If you look at the sample, by the measure only method all calculations are wrong. By the calculated column way, Machine1 seems to have the proper MTBF, but Machine2 is way off...

 

Calculated column has this code:

 

 

Uptime = 

VAR next =
    MINX (
        FILTER (
            'TABLE',
            'TABLE'[Machine] = EARLIER ( 'TABLE'[Machine] )
                && 'TABLE'[actstart] > EARLIER ( 'TABLE'[actstart] )
                && 'TABLE'[failurecode] = "A1"
        ),
        'TABLE'[actstart]
    )
RETURN
    IF (
        'TABLE'[failurecode] <> "A1",
        0,
        IF (
            ISBLANK ( next ),
            DATEDIFF ( 'TABLE'[actfinish], NOW (), SECOND ),
            DATEDIFF ( 'TABLE'[actfinish], next, SECOND )
        )
    )

 

 

 
Measure has this code:

 

 

MTBF (Hours) = DIVIDE(SUM('TABLE'[Uptime]),[Repairs],BLANK())/3600

 

 

 
By the Measure only method this is the code:

 

 

MTBF (Hours) Measure = 
VAR __table = 'TABLE'
VAR __table1 = 
            ADDCOLUMNS(__table,"__next",
                MINX(
                    FILTER(__table,
                            'TABLE'[Machine]=EARLIER('TABLE'[Machine]) && 
                            'TABLE'[Machine] = EARLIER ( 'TABLE'[Machine] ) && 
                            'TABLE'[failurecode]="A1"
                    ),
                    'TABLE'[actstart]
                )
            )
VAR __table2 = ADDCOLUMNS(__table1,"__uptime",
                    IF('TABLE'[failurecode]<>"A1",
                        0,
                        IF(ISBLANK([__next]),
                            DATEDIFF('TABLE'[actfinish],NOW(),SECOND),
                            DATEDIFF('TABLE'[actfinish],[__next],SECOND)
                        )
                    )
                )
VAR __repairs = CALCULATE(COUNTROWS('TABLE'),FILTER(ALLSELECTED('TABLE'),'TABLE'[failurecode]="A1"))
RETURN
DIVIDE(SUMX(__table2,[__uptime]),__repairs,BLANK())/3600

 

 

 

Sample:

https://www.dropbox.com/s/k9a7ikcxfnwswhl/Sample.xlsx?dl=0