topic Re: Better approach to this calculated measure? in DAX Commands and Tips

Better approach to this calculated measure?

unknown917 — Thu, 24 Oct 2024 14:52:07 GMT

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!

Re: Better approach to this calculated measure?

bhanu_gautam — Thu, 24 Oct 2024 15:44:54 GMT

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

Re: Better approach to this calculated measure?

Anonymous — Fri, 25 Oct 2024 06:38:06 GMT

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.

Re: Better approach to this calculated measure?

unknown917 — Fri, 25 Oct 2024 11:21:23 GMT

makes sense! thank you!!