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 ...
Need Help
  • SpartaBI's avatar
    SpartaBI
    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
    )

     

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

qmartiny I did it really quick and I can't check the code for QA but 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(
	        	ALL(
	        	'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(
	        	ALL(
	        	'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
    )

 


Even if I missed something I think you'll see the main issue:
I changed all your filters from running their filters on the entire tables (BIG NO NO 😄) to only running them on the columns you used for conditions.

Let me know how it went
 

qmartiny's avatar
qmartiny
Helper III
4 years ago

Hi SpartaBI  !

 

Thank you very much for looking into this !

 

 

I am getting the error "All column arguments of the ALL/ALLNOBLANKROW/ALLSELECTED function must be from the same table."

 

 

UPDATE :

Error appears on the FY_DME.

 

UPDATE 2 :
Even removing this seems to throw an error anyway. logic seems ok though

VAR FY_DME =
    CALCULATE (
        SUM ( 'CX Cloud Pipeline'[acv €] ),
        KEEPFILTERS(
        	FILTER(
	        	ALL(
	        	'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 )
        	)
        )
    )

I appreciate if you have any thoughts. I will continue to look on my side.


Have a great day