Measure in Filter, very slow calc

[KirillS]

Hello everyone, I have a table of 1.5 million rows with telemetry data. Granularity of 1 day per device.

I need to calculate the device operation flag by condition. Next, determine the malfunction of the devices by this flag and other conditions.

There are two measures:
1) Work days - calculate the number of working days of the devices
2) Engine Error - we determine the fact of malfunction

Work days = 
VAR calc = CALCULATE (
            COUNTROWS ( 'Statistic' ),
            KEEPFILTERS (
            FILTER (
                ALL (
                    'Statistic'[movement_mileage_km],
                    'Statistic'[total_consumption_l],
                    'Statistic'[motohours_duration_h]
                ),
                'Statistic'[movement_mileage_km] >= 5
                    || 'Statistic'[total_consumption_l] >= 5
                    || ( 'Statistic'[motohours_duration_h] >= 0.5
                    && 'Statistic'[motohours_duration_h] < 24 )
            )
        )
    )
RETURN
calc

Engine Errors =   
CALCULATE (
        COUNTROWS ( 'Statistics' ),
        KEEPFILTERS (
            FILTER (
                ALL ( 'Statistics'[motohours_duration_h], 'Statistics'[motohours_duration_h] ),
                ([Work days] > 0 && 'Statistics'[motohours_duration_h] == 0 ) 
                || 'Statistics'[motohours_duration_h] >= 24 
            )
        )
    )

Engine Errors execute time 80 seconds. If i delete [Work days] > 0 contition execute time - 4 seconds. Does anyone have any idea how to optimize the second query? 

I need it to be a measure (not a calculated column) and the calculations are isolated from each other (in different measures), due to the fact that the flag is still used in a large number of calculations and will be parameterized.

Any ideas?

[wdx223_Daniel · Accepted solution]

Engine Errors =
VAR _ids =
    FILTER ( VALUES ( 'Statistics'[ID] ), [Work days] > 0 )
RETURN
    COUNTROWS (
        FILTER (
            'Statistics',
            ( 'Statistics'[ID]
                IN _ids
                && 'Statistics'[motohours_duration_h] == 0 )
                || 'Statistics'[motohours_duration_h] >= 24
        )
    )​

Work days =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Statistic',
            'Statistic'[movement_mileage_km],
            'Statistic'[total_consumption_l],
            'Statistic'[motohours_duration_h]
        ),
        'Statistic'[movement_mileage_km] >= 5
            || 'Statistic'[total_consumption_l] >= 5
            || ( 'Statistic'[motohours_duration_h] >= 0.5
            && 'Statistic'[motohours_duration_h] < 24 )
    )
)

[ppm1]

In the second measure, you are executing the Work Days measure on every row of a big table. Below is one suggestion to significantly reduce that granularity. Not sure if the logic matches your business need but hopefully it will give you an idea of the type of change needed.

Engine Errors =
VAR deviceswithworkdays =
    FILTER ( DISTINCT ( 'Statistics'[DeviceID] ), [Work Days] > 0 )
RETURN
    SUMX (
        deviceswithworkdays,
        CALCULATE (
            COUNTROWS ( 'Statistics' ),
            'Statistics'[motohours_duration_h] == 0
                || 'Statistics'[motohours_duration_h] >= 24
        )
    )

Pat

[v-rongtiep-msft]

Hi @KirillS ,

Please have a try.

Modify your measures.

Work days = 
 CALCULATE (
            COUNTROWS ( 'Statistic' ),
            KEEPFILTERS(
            FILTER (
               
                    Statistic
                ,
                'Statistic'[movement_mileage_km] >= 5
                    || 'Statistic'[total_consumption_l] >= 5
                    || ( 'Statistic'[motohours_duration_h] >= 0.5
                    && 'Statistic'[motohours_duration_h] < 24 )
            )
        )
 )

Engine Errors = 
CALCULATE (
        COUNTROWS (Statistic ),
        KEEPFILTERS (
            FILTER (
                 Statistic,
                ([Work days1] > 0 && Statistic[motohours_duration_h] == 0 ) 
                || Statistic[motohours_duration_h] >= 24 
            )
        )
    )

If I have misundersood your meaning, please provide more details.

Best Regards

Community Support Team _ Polly

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