Better approach to this calculated measure?

[unknown917]

I'm running into a resource error in my visual and thinking the below measure may be the culprit.  Does anyone know of a more streamlined approach to this?

Max Possible Hits = VAR _CalcSingle =

SWITCH(

    TRUE(),

    'Table'[1 stp threshold]>=0 && 'Table'[1 stp threshold]<=19,13,

    'Table'[1 stp threshold]>=20 && 'Table'[1 stp threshold]<=38,26,

    'Table'[1 stp threshold]>=39 && 'Table'[1 stp threshold]<=77,52,

    'Table'[1 stp threshold]>=78 && 'Table'[1 stp threshold]<=129,104,

    'Table'[1 stp threshold]>=130 && 'Table'[1 stp threshold]<=181,156,

    'Table'[1 stp threshold]>=182 && 'Table'[1 stp threshold]<=233,208,

    'Table'[1 stp threshold]>=234 && 'Table'[1 stp threshold]<=285,260,

    'Table'[1 stp threshold]>=286 && 'Table'[1 stp threshold]<=337,312,

    'Table'[1 stp threshold]>=338 && 'Table'[1 stp threshold]<=4569,364)

VAR _CalcMulti =

SWITCH(

    TRUE(),

    'Table'[>1 stp/wk threshold]>=0 && 'Table'[>1 stp/wk threshold]<=19,13,

    'Table'[>1 stp/wk threshold]>=20 && 'Table'[>1 stp/wk threshold]<=38,26,

    'Table'[>1 stp/wk threshold]>=39 && 'Table'[>1 stp/wk threshold]<=77,52,

    'Table'[>1 stp/wk threshold]>=78 && 'Table'[>1 stp/wk threshold]<=129,104,

    'Table'[>1 stp/wk threshold]>=130 && 'Table'[>1 stp/wk threshold]<=181,156,

    'Table'[>1 stp/wk threshold]>=182 && 'Table'[>1 stp/wk threshold]<=233,208,

    'Table'[>1 stp/wk threshold]>=234 && 'Table'[>1 stp/wk threshold]<=285,260,

    'Table'[>1 stp/wk threshold]>=286 && 'Table'[>1 stp/wk threshold]<=337,312,

    'Table'[>1 stp/wk threshold]>=338 && 'Table'[>1 stp/wk threshold]<=4569,364)

RETURN

    SWITCH(

        TRUE(),

        _CalcSingle<52,_CalcSingle,

       _CalcMulti<52,52,_CalcMulti)

Any help will be much appreciated!

[Anonymous · Accepted solution]

Hi All,
Firstly  bhanu_gautam thank you for your solution!
And @unknown917 , I tried to simplify your code a bit, not so much conditional judgment , to reduce your code redundancy, to see if it can solve your problem!

Max Possible Hits(column) = 
VAR _CalcSingle =
    SWITCH(
        TRUE(),
        'Table'[1 stp threshold] <= 19, 13,
        'Table'[1 stp threshold] <= 38, 26,
        'Table'[1 stp threshold] <= 77, 52,
        'Table'[1 stp threshold] <= 129, 104,
        'Table'[1 stp threshold] <= 181, 156,
        'Table'[1 stp threshold] <= 233, 208,
        'Table'[1 stp threshold] <= 285, 260,
        'Table'[1 stp threshold] <= 337, 312,
        'Table'[1 stp threshold] <= 4569, 364
    )

VAR _CalcMulti =
    SWITCH(
        TRUE(),
        'Table'[>1 stp/wk threshold] <= 19, 13,
        'Table'[>1 stp/wk threshold] <= 38, 26,
        'Table'[>1 stp/wk threshold] <= 77, 52,
        'Table'[>1 stp/wk threshold] <= 129, 104,
        'Table'[>1 stp/wk threshold] <= 181, 156,
        'Table'[>1 stp/wk threshold] <= 233, 208,
        'Table'[>1 stp/wk threshold] <= 285, 260,
        'Table'[>1 stp/wk threshold] <= 337, 312,
        'Table'[>1 stp/wk threshold] <= 4569, 364
    )

RETURN 
    IF(
     'Table'[1 stp threshold]< 52,
    _CalcSingle, 
    IF('Table'[>1 stp/wk threshold]<52,
    52,
_CalcMulti))

If you have further questions, you can contact me at any time, I will reply to you as soon as I receive your message, I look forward to hearing from you!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[unknown917]

makes sense!  thank you!!

[bhanu_gautam]

@unknown917 , Try using

Max Possible Hits =
VAR Thresholds =
{
(0, 19, 13),
(20, 38, 26),
(39, 77, 52),
(78, 129, 104),
(130, 181, 156),
(182, 233, 208),
(234, 285, 260),
(286, 337, 312),
(338, 4569, 364)
}

VAR GetMaxHits =
VAR CurrentThreshold = 'Table'[1 stp threshold]
RETURN
MAXX(
FILTER(
Thresholds,
CurrentThreshold >= [Value1] && CurrentThreshold <= [Value2]
),
[Value3]
)

VAR GetMaxHitsMulti =
VAR CurrentThresholdMulti = 'Table'[>1 stp/wk threshold]
RETURN
MAXX(
FILTER(
Thresholds,
CurrentThresholdMulti >= [Value1] && CurrentThresholdMulti <= [Value2]
),
[Value3]
)

RETURN
IF(
GetMaxHits < 52,
GetMaxHits,
IF(
GetMaxHitsMulti < 52,
52,
GetMaxHitsMulti
)
)