Improve Measure Performance with nested IF(CALCULATE()

[qmartiny]

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 underlying data line by line in a table.

Any idea how this measure could be improved? I realise it's probably not the most efficient. Using SWITCH(TRUE()) seems a bit better

Thanks a lot.

Contrib FlexQ SUMMARY =
VAR selectperiod =
    SELECTEDVALUE ( '** Period Create'[Period] )
VAR selectcontrib =
    SELECTEDVALUE ( '** Contributor'[Contrib] )
VAR periodq =
    VALUE ( MID ( SELECTEDVALUE ( '** Period Create'[Period] ), 2, 1 ) )
VAR Q1_DME =
    CALCULATE (
        SUM ( 'CX Cloud Pipeline'[acv €] ),
        FILTER (
            'CX Cloud Pipeline',
            'CX Cloud Pipeline'[DME Handover Quarter] = "2022-Q1"
        )
    )
VAR Q2_DME =
    CALCULATE (
        SUM ( 'CX Cloud Pipeline'[acv €] ),
        FILTER (
            'CX Cloud Pipeline',
            '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 €] ),
        FILTER (
            'CX Cloud Pipeline',
            '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 €] ),
        FILTER (
            'CX Cloud Pipeline',
            'CX Cloud Pipeline'[Creation Quarter] = "2022-Q1"
        )
    )
VAR Q2 =
    CALCULATE (
        SUM ( 'CX Cloud Pipeline'[acv €] ),
        FILTER (
            'CX Cloud Pipeline',
            'CX Cloud Pipeline'[Creation Quarter] = "2022-Q2"
        )
    )
VAR FY =
    CALCULATE (
        SUM ( 'CX Cloud Pipeline'[acv €] ),
        FILTER ( 'CX Cloud Pipeline', 'CX Cloud Pipeline'[Creation Year] = 2022 )
    )
VAR R4Q =
    CALCULATE (
        SUM ( 'CX Cloud Pipeline'[acv €] ),
        FILTER (
            'CX Cloud Pipeline',
            'CX Cloud Pipeline'[Closing Quarter] = "2022-Q2"
                || 'CX Cloud Pipeline'[Closing Quarter] = "2022-Q3"
                || 'CX Cloud Pipeline'[Closing Quarter] = "2022-Q4"
                || 'CX Cloud Pipeline'[Closing Quarter] = "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 · Accepted solution]

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
    )

[qmartiny]

Hi @SpartaBI,, now using this measure in a table, I have to add a visual filter for "measure is not blank" since the table returns all rows, just with a blank value where the measure isnt in scope.

Do you have any idea how to go around that without using the visual filter? I realise there might be a better way.

Thanks a lot again

[SpartaBI]

Not sure I understand what you are experiencing, but you can try replacing the 'ALL' functions with 'ALLNOBLANKROW". Maybe that will do the trick

[qmartiny]

Here's an example using that measure in a table - I guess it's because it's just pulling just the table and adding the measure on top doesnt tell powerbi to only show the rows where the measure isnt empty

[SpartaBI]

Is that only measure in your table? What is the Link Icon there? It's another url value from a differnet measure, right? If yes, than it's not our measure fault you see that row, and yes you have to use that visual level filter or to filter all other measures to blank when our measure is blank. Hard to guess which option will be better. Need to test both and see which one is faster 🙂

[SpartaBI]

@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]

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