Forum Discussion

qmartiny's avatar
qmartiny
Helper III
4 years ago
Solved

Improve Measure Performance with nested IF(CALCULATE()

Hello, I have the need to display different CALCULATE(SUM() functions depending on user selection (period, 'contributor') It works, however the performance is very poor when I try to display the ...
  • SpartaBI's avatar
    4 years ago

    I'm gussing becasue of the auto date time table behind the [Phase Since Date] . Better if you had a proper date table or a simple coulmn for the quarter like you have for the [DME Handover Quarter] column


    Again, without looking at your model, I'm actually gussing by your code how it looks like ‌‌ so try this:

    Contrib FlexQ SUMMARY =
    VAR selectperiod =
        SELECTEDVALUE ( '** Period Create'[Period] )
    VAR selectcontrib =
        SELECTEDVALUE ( '** Contributor'[Contrib] )
    VAR periodq =
        VALUE ( MID ( selectperiod, 2, 1 ) )
    VAR Q1_DME =
        CALCULATE (
            SUM ( 'CX Cloud Pipeline'[acv €] ),
    		KEEPFILTERS('CX Cloud Pipeline'[DME Handover Quarter] = "2022-Q1")
        )
    VAR Q2_DME =
        CALCULATE (
            SUM ( 'CX Cloud Pipeline'[acv €] ),
            KEEPFILTERS(
            	FILTER(
            		SUMMARIZE(
            			ALL('CX Cloud Pipeline'),
    	        		'CX Cloud Pipeline'[DME Handover Quarter],
    	        		'CX Cloud Pipeline'[Phase Since Date].[QuarterNo],
    	        		'CX Cloud Pipeline'[Phase Since Date].[Year]
    	        	),
    	            'CX Cloud Pipeline'[DME Handover Quarter] = BLANK ()
    	                && 'CX Cloud Pipeline'[Phase Since Date].[QuarterNo] = 2
    	                && 'CX Cloud Pipeline'[Phase Since Date].[Year] = 2022
    	           )
            )
        )
    VAR FY_DME =
        CALCULATE (
            SUM ( 'CX Cloud Pipeline'[acv €] ),
            KEEPFILTERS(
            	FILTER(
            		SUMMARIZE(
            			ALL('CX Cloud Pipeline'),
    	        		'CX Cloud Pipeline'[DME Handover Quarter],
    	        		'CX Cloud Pipeline'[Phase Since Date].[QuarterNo],
    	        		'CX Cloud Pipeline'[Phase Since Date].[Year]
    	        	),
                'CX Cloud Pipeline'[DME Handover Quarter] = "2022-Q1"
                    || ( 'CX Cloud Pipeline'[Phase Since Date].[QuarterNo] = 2
                    && 'CX Cloud Pipeline'[Phase Since Date].[Year] = 2022 )
            	)
            )
        )
    VAR Q1 =
        CALCULATE (
            SUM ( 'CX Cloud Pipeline'[acv €] ),
    		KEEPFILTERS('CX Cloud Pipeline'[Creation Quarter] = "2022-Q1")
        )
    VAR Q2 =
        CALCULATE (
            SUM ( 'CX Cloud Pipeline'[acv €] ),
    		KEEPFILTERS('CX Cloud Pipeline'[Creation Quarter] = "2022-Q2")
        )
    VAR FY =
        CALCULATE (
            SUM ( 'CX Cloud Pipeline'[acv €] ),
            KEEPFILTERS('CX Cloud Pipeline'[Creation Year] = 2022 )
        )
    VAR R4Q =
        CALCULATE (
            SUM ( 'CX Cloud Pipeline'[acv €] ),
    			KEEPFILTERS('CX Cloud Pipeline'[Closing Quarter] IN { "2022-Q2", "2022-Q3", "2022-Q4", "2023-Q1"} ))
    RETURN
        SWITCH (
            TRUE (),
            selectperiod = "R4Q", R4Q,
            selectperiod = "Q122"
                && selectcontrib = "DME", Q1_DME,
            selectperiod = "Q222"
                && selectcontrib = "DME", Q2_DME,
            selectperiod = "FY22"
                && selectcontrib = "DME", FY_DME,
            selectperiod = "Q122"
                && selectcontrib <> "DME", Q1,
            selectperiod = "Q222"
                && selectcontrib <> "DME", Q2,
            selectperiod = "FY22"
                && selectcontrib <> "DME", FY
        )