Forum Discussion
Improve Measure Performance with nested IF(CALCULATE()
- 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 )
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
)
- qmartiny4 years agoHelper III
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- SpartaBI4 years agoCommunity Champion
Not sure I understand what you are experiencing, but you can try replacing the 'ALL' functions with 'ALLNOBLANKROW". Maybe that will do the trick
- qmartiny4 years agoHelper III
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