cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Optimization of a Measure - Need help

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 ) )

 

 

2 REPLIES 2
Highlighted
Super User III
Super User III

Re: Optimization of a Measure - Need help

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 

SU18_powerbi_badge

Highlighted
Post Prodigy
Post Prodigy

Re: Optimization of a Measure - Need help

@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? 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors