Optimization of a Measure - Need help

[gil12]

Hi there, Can someone please help me optimize this measure? Now it takes very long to display results when used in a table or a matrix and it's very slow.

It's a "Status" measure, which shows status of a certain KPI in (-1,0,1) when comparing it with target.

Thanks in Advance!

_KPI Status_Target =
VAR _measureLowisGood = [MeasureID Selected] IN { 1, 2, 3 } // List all the measures where if the actual value if below target is considered good (example - Lost time Injuries)
VAR _measureHighisGood = [MeasureID Selected] IN { 6, 4, 5 } // List all the measures where if the actual value if above target is considered good (example - OTP)
VAR _Measure = [MeasureID Selected] // Measure selected by filter or which is already in a table or a matrix
VAR _bottomthreshold =
    CALCULATE (
        VALUES ( '1-DimKPICat'[Lower Threshold] ),
        FILTER ( '1-DimKPICat', '1-DimKPICat'[KPIID] = _Measure )
    )
VAR _topthreshold =
    CALCULATE (
        VALUES ( '1-DimKPICat'[Upper Threshold] ),
        FILTER ( '1-DimKPICat', '1-DimKPICat'[KPIID] = _Measure )
    )
VAR _High_is_Good =
    SWITCH (
        TRUE (),
        [_KPI value_Target] < _bottomthreshold, -1,
        [_KPI value_Target] > _topthreshold, 1,
        [_KPI value_Target] >= _bottomthreshold
            && [_KPI value_Target] <= _topthreshold, 0
    )
VAR _Low_is_Good =
    SWITCH (
        TRUE (),
        [_KPI value_Target] < _bottomthreshold, 1,
        [_KPI value_Target] > _topthreshold, -1,
        [_KPI value_Target] >= _bottomthreshold
            && [_KPI value_Target] <= _topthreshold, 0
    )
RETURN
    IF ( _measureLowisGood, _Low_is_Good, IF ( _measureHighisGood, _High_is_Good ) )

[sanimesa]

@gil12 Can you please post sample data or a picture of your table where you place this measure? Also, how many rows does this table have? 

[AlB]

Hi @gil12 

The measure does not seem too complex. Is [_KPI value_Target] a measure? If so you are invoking it many times. You could just invoke it once and store its result in a variable and use that variable consequently. Same for [MeasureID Selected]. It's hard to say more without seeing the code fro those measures or more info on the data model. Something like:

_KPI Status_Target =
VAR _Measure = [MeasureID Selected] // Measure selected by filter or which is already in a table or a matrix
VAR _measureLowisGood = _Measure IN { 1, 2, 3 } // List all the measures where if the actual value if below target is considered good (example - Lost time Injuries)
VAR _measureHighisGood = _Measure IN { 6, 4, 5 } // List all the measures where if the actual value if above target is considered good (example - OTP)
VAR _bottomthreshold =
    CALCULATE (
        VALUES ( '1-DimKPICat'[Lower Threshold] ),
        FILTER ( '1-DimKPICat', '1-DimKPICat'[KPIID] = _Measure )
    )
VAR _topthreshold =
    CALCULATE (
        VALUES ( '1-DimKPICat'[Upper Threshold] ),
        FILTER ( '1-DimKPICat', '1-DimKPICat'[KPIID] = _Measure )
    )
VAR var_KPI_value_Target_ = [_KPI value_Target] //CReate this variable to invoke the measure only once
VAR _High_is_Good =
    SWITCH (
        TRUE (),
        var_KPI_value_Target_ < _bottomthreshold, -1,
        var_KPI_value_Target_ > _topthreshold, 1,
        var_KPI_value_Target_ >= _bottomthreshold
            && var_KPI_value_Target_ <= _topthreshold, 0
    )
VAR _Low_is_Good =
    SWITCH (
        TRUE (),
        var_KPI_value_Target_ < _bottomthreshold, 1,
        var_KPI_value_Target_ > _topthreshold, -1,
        var_KPI_value_Target_ >= _bottomthreshold
            && var_KPI_value_Target_ <= _topthreshold, 0
    )
RETURN
    IF ( _measureLowisGood, _Low_is_Good, IF ( _measureHighisGood, _High_is_Good ) )

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers