Forum Discussion
Helper IIIntersect formula to sum up values
Helper IIThanks so much AlexisOlson!! I had no idea using variables could improve the performance so much (a visual that used to take 45 seconds to run now takes 8 seconds!). It solves my problem for sure!
One additional question if I may.. I would like to add two flexible dynamics in the formula (so that account status is based on what the user selects as Account level and Product level - ultimately different aggregation of accounts and group of products), do you see anything to optimaize more in the formula below? The fprmula performance with these two additional flexibilities is not bad, I just thought in case you have any ideas to improve it further 🙂
Thanks so much!!
- AlexisOlson
Super User
I can't think of something much better. You could move stuff around to make it look different but I don't see a way to avoid the general structure.
You'll likely get better performance filtering on single columns rather than entire tables though. See if this is any faster:
Account status growth (with account and product lvl) = VAR CurrStatus = SELECTEDVALUE ( 'Account status'[Account status] ) VAR CurrAccountlvl = SELECTEDVALUE ( 'Account level'[Account level] ) VAR CurrProductlvl = SELECTEDVALUE ( 'Selection: product level'[Product level] ) VAR PlatformCAC = TREATAS ( { CurrStatus }, 'Account_Status_Table_Platform'[Account status Platform] ) VAR PlatformPAC = TREATAS ( { CurrStatus }, 'Paccount_Status_Table_Platform'[Parent Account status platform] ) VAR ProductCAC = TREATAS ( { CurrStatus }, 'Account_Status_Table_Product'[Account status product] ) VAR ProductPAC = TREATAS ( { CurrStatus }, 'Paccount_Status_Table_Product'[Parent Account status product] ) VAR SegmentCAC = TREATAS ( { CurrStatus }, 'Account_Status_Table_Segment'[Account status segment] ) VAR SegmentPAC = TREATAS ( { CurrStatus }, 'Paccount_Status_Table_Segment'[Parent Account status segment] ) RETURN SWITCH ( CurrProductlvl, "Platform", SWITCH ( CurrAccountlvl, "CAC", CALCULATE ( [Growth], PlatformCAC ), "PAC", CALCULATE ( [Growth], PlatformPAC ) ), "Product", SWITCH ( CurrAccountlvl, "CAC", CALCULATE ( [Growth], ProductCAC ), "PAC", CALCULATE ( [Growth], ProductPAC ) ), "Segment", SWITCH ( CurrAccountlvl, "CAC", CALCULATE ( [Growth], SegmentCAC ), "PAC", CALCULATE ( [Growth], SegmentPAC ) ) )- apohl1
Thank you for the advice! Unfortunately the suggested formula doens't work because all account status forumlas such as "Account_Status_Table_Platform'[Account status Platform]" are measures. They are measures and not columns because they should change based on user selection of point in time and time comparison.
- apohl1
I wonder if you are able to help me on another formula? In the formula below I want to sum up sales growth from "Gained" and "Lost" accounts but the performance is really bad (I think it's because I use FILTER function).. Thanks a million!! 🙂
Growth through Net account conversion =VAR CurrAccountlvl =SELECTEDVALUE ( 'Account level'[Account level] )VAR CurrProductlvl =SELECTEDVALUE ( 'Selection: product level'[Product level] )RETURNSWITCH(CurrProductlvl,"Platform",SWITCH(CurrAccountlvl,"CAC",CALCULATE ([Growth],FILTER ( 'Account_Status_Table_Platform', [Account status Platform] = "Gained" ))+CALCULATE ([Growth],FILTER ( 'Account_Status_Table_Platform', [Account status Platform] = "Lost" )),"PAC",CALCULATE ([Growth],FILTER ( 'Paccount_Status_Table_Platform', [Parent Account status platform] = "Gained" ))+CALCULATE ([Growth],FILTER ( 'Paccount_Status_Table_Platform', [Parent Account status platform] = "Lost" ))),"Product",SWITCH(CurrAccountlvl,"CAC",CALCULATE ([Growth],FILTER ( 'Account_Status_Table_Product', [Account status product] = "Gained" ))+CALCULATE ([Growth],FILTER ( 'Account_Status_Table_Product', [Account status product] = "Lost" )),"PAC",CALCULATE ([Growth],FILTER ( 'Paccount_Status_Table_Product', [Parent Account status product] = "Gained" ))+CALCULATE ([Growth],FILTER ( 'Paccount_Status_Table_Product', [Parent Account status product] = "Lost" ))),"Segment",SWITCH(CurrAccountlvl,"CAC",CALCULATE ([Growth],FILTER ( 'Account_Status_Table_Segment', [Account status segment] = "Gained" ))+CALCULATE ([Growth],FILTER ( 'Account_Status_Table_Segment', [Account status segment] = "Lost" )),"PAC",CALCULATE ([Growth],FILTER ( 'Paccount_Status_Table_Segment', [Parent Account status segment] = "Gained" ))+CALCULATE ([Growth],FILTER ( 'Paccount_Status_Table_Segment', [Parent Account status segment] = "Lost" ))))- AlexisOlson
Using measures inside of a FILTER function on an entire table is computationally expensive since it means evaluating the measure for every single row of that table. Depending on how these measures are defined, there might be much more efficient ways to do this. What do these measures look like?
You should at least be able to combine the Gained and Lost pieces like this:
CALCULATE ( [Growth], FILTER ( 'Table1', [Measure1] IN { "Gained", "Lost" } ) )
