Forum Discussion
Paula1323
4 years agoNew Member
MEASUREMENT OPTIMIZATION USING SUMX
- 4 years ago
Hi Paula1323
Using variables will speed it a little. However, without further details won't be able to provide you with a definitive answer. Seems like the row level SUMX can be avoided at least the measures inside it can be avoided.Generated Reduced PT = IF ( HASONEVALUE ( 'Generated PT'[BK_MISSION] ), VAR Case1 = [Case_1] VAR Case2 = [Case_2] VAR Case3 = [Case_3] VAR Case4 = [Case_4] VAR Case5 = [Case_5] VAR Case6 = [Case_6] RETURN SWITCH ( TRUE (), Case1 > 0, Case1, Case2 > 0, Case2, Case3 > 0, Case3, Case4 > 0, Case4, Case5 > 0, Case5, Case6 > 0, Case6 ), CALCULATE ( SUMX ( 'Mission (Attributes)', VAR Case1 = [Case_1] VAR Case2 = [Case_2] VAR Case3 = [Case_3] VAR Case4 = [Case_4] VAR Case5 = [Case_5] VAR Case6 = [Case_6] RETURN SWITCH ( TRUE (), Case1 > 0, Case1, Case2 > 0, Case2, Case3 > 0, Case3, Case4 > 0, Case4, Case5 > 0, Case5, Case6 > 0, Case6 ) ), FILTER ( 'Mission (Attributes)', 'Mission (Attributes)'[Company] = "PT" ) ) )
tamerj1
4 years agoCommunity Champion
Hi Paula1323
Using variables will speed it a little. However, without further details won't be able to provide you with a definitive answer. Seems like the row level SUMX can be avoided at least the measures inside it can be avoided.
Generated Reduced PT =
IF (
HASONEVALUE ( 'Generated PT'[BK_MISSION] ),
VAR Case1 = [Case_1]
VAR Case2 = [Case_2]
VAR Case3 = [Case_3]
VAR Case4 = [Case_4]
VAR Case5 = [Case_5]
VAR Case6 = [Case_6]
RETURN
SWITCH (
TRUE (),
Case1 > 0, Case1,
Case2 > 0, Case2,
Case3 > 0, Case3,
Case4 > 0, Case4,
Case5 > 0, Case5,
Case6 > 0, Case6
),
CALCULATE (
SUMX (
'Mission (Attributes)',
VAR Case1 = [Case_1]
VAR Case2 = [Case_2]
VAR Case3 = [Case_3]
VAR Case4 = [Case_4]
VAR Case5 = [Case_5]
VAR Case6 = [Case_6]
RETURN
SWITCH (
TRUE (),
Case1 > 0, Case1,
Case2 > 0, Case2,
Case3 > 0, Case3,
Case4 > 0, Case4,
Case5 > 0, Case5,
Case6 > 0, Case6
)
),
FILTER ( 'Mission (Attributes)', 'Mission (Attributes)'[Company] = "PT" )
)
)