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