Forum Discussion
Paula1323
4 years agoNew Member
MEASUREMENT OPTIMIZATION USING SUMX
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" ) ) )
2 Replies
- tamerj1Community 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" ) ) ) - AnonymousNot applicable
HI Paula1323,
It sounds like you nested multiple iterators in your measure expression, perhaps you can take a look at the following blog which told about this and how to optimize if helps:
Optimizing nested iterators in DAX - SQLBI
Regards,
Xiaoxin Sheng