Forum Discussion

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 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
    )

Need Help

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
    )

7 Replies

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

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

SpartaBI

Community Champion

4 years ago

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
Helper III
4 years ago
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
  Community Champion
  4 years ago
  Not sure I understand what you are experiencing, but you can try replacing the 'ALL' functions with 'ALLNOBLANKROW". Maybe that will do the trick

Forum Discussion

Improve Measure Performance with nested IF(CALCULATE()

7 Replies

Related content

Need Performance Improvement Suggestions

Improve performances for Nested.Join

Recommendations to Improve Power BI Incremental Refresh Performance

Recommendations to Improve Power BI Incremental Refresh Performance

Visual calculation- If