This calculated column makes everything slow

Question

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/ba-p/339586

After this, opening the PBIX file, creating measures or columns is slow. It takes minutes for the "Working on it" popup to disappear.

Any solution to improve performance on this?

Thanks

alexisolson · Answer

Note that Greg's measure will have similar performance issues (compared with the calculated column) but using it instead of a calculated column will keep you from needing to recalculate it each time you edit your model.
&nbsp;
Using the new OFFSET function, here's a version that's about 30x faster (~6 ms vs ~180 ms) according to my testing against the pbix attached to the post I linked. It should be much better for large models as well as more dynamically adaptable to various filters (plus it only needs to calculate over all selected data rather than the entire fact table).
MTBF (Hours) AO =
VAR _Summary_ =
    CALCULATETABLE (
        SUMMARIZE (
            Repairs,
            Repairs[MachineName],
            Repairs[RepairStarted],
            Repairs[RepairCompleted]
        ),
        KEEPFILTERS ( Repairs[RepairType] &lt;&gt; "PM" )
    )
VAR _AddNext_ =
    ADDCOLUMNS (
        _Summary_,
        "@NextRepair",
            SELECTCOLUMNS (
                OFFSET (
                    1,
                    _Summary_,
                    ORDERBY ( Repairs[RepairStarted] ),
                    PARTITIONBY ( Repairs[MachineName] )
                ),
                "RepairStarted", [RepairStarted]
            )
    )
VAR _Uptime_ =
    ADDCOLUMNS (
        _AddNext_,
        "@Uptime",
            DATEDIFF (
                [RepairCompleted],
                COALESCE ( [@NextRepair], NOW () ),
                SECOND
            )
    )
RETURN
    AVERAGEX ( _Uptime_, [@Uptime] ) / 3600

alexisolson · Answer

I think what's happening is that every time you edit the model, it's recomputing the calculated column, which wasn't written especially efficiently. Defining this as a measure without using calculated columns should help and&nbsp;Greg_Deckler&nbsp;has another post about that here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Mean-Time-Between-Failure-MTBF/m-p/625082

some1else · Answer

Wow, thanks for the extensive reply, I will look into it tonight.
Using the calculated column and the measure I get different results:

Even the calculated column has some weird values. Look at the 5 and 6th rows, the MTBF should be 0, not 2283hours which is the MTBF between the 7 and 6th row. Any ideas?

greg_deckler · Answer

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

some1else · Answer

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...&nbsp;Calculated column has this code:&nbsp;&nbsp;Uptime =

VAR next =
    MINX (
        FILTER (
            'TABLE',
            'TABLE'[Machine] = EARLIER ( 'TABLE'[Machine] )
                &amp;&amp; 'TABLE'[actstart] &gt; EARLIER ( 'TABLE'[actstart] )
                &amp;&amp; 'TABLE'[failurecode] = "A1"
        ),
        'TABLE'[actstart]
    )
RETURN
    IF (
        'TABLE'[failurecode] &lt;&gt; "A1",
        0,
        IF (
            ISBLANK ( next ),
            DATEDIFF ( 'TABLE'[actfinish], NOW (), SECOND ),
            DATEDIFF ( 'TABLE'[actfinish], next, SECOND )
        )
    )&nbsp;&nbsp;&nbsp;Measure has this code:&nbsp;&nbsp;MTBF (Hours) = DIVIDE(SUM('TABLE'[Uptime]),[Repairs],BLANK())/3600&nbsp;&nbsp;&nbsp;By the Measure only method this is the code:&nbsp;&nbsp;MTBF (Hours) Measure = 
VAR __table = 'TABLE'
VAR __table1 = 
            ADDCOLUMNS(__table,"__next",
                MINX(
                    FILTER(__table,
                            'TABLE'[Machine]=EARLIER('TABLE'[Machine]) &amp;&amp; 
                            'TABLE'[Machine] = EARLIER ( 'TABLE'[Machine] ) &amp;&amp; 
                            'TABLE'[failurecode]="A1"
                    ),
                    'TABLE'[actstart]
                )
            )
VAR __table2 = ADDCOLUMNS(__table1,"__uptime",
                    IF('TABLE'[failurecode]&lt;&gt;"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&nbsp;&nbsp;&nbsp;Sample:https://www.dropbox.com/s/k9a7ikcxfnwswhl/Sample.xlsx?dl=0

Forum Discussion

This calculated column makes everything slow

5 Replies

Related content

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

IF calculated column

Power Query or Calculated columns

Dynamic Variable in Calculated Columns