![]() ![]() Yikes! Imagine updating thousands of these! I’m going cross-eyed already! Slicer_contest,slicer_supplier,slicer_supplier1,slicer_supplier5,slicer_salesterritory =CUBEVALUE(“ThisWorkbookDataModel”,”.”,slicer_country1,slicer_country2,slicer_dim_calendar,slicer_salesrep, What’s more, not all of the slicer names in the scorecard were to be included in the calculations and yet more slicers were set to be added in the future! The scorecard had a total of 10 slicers, and each needed to be referenced in the formulas. The first thing I noticed when opening my scorecard was the length of the cube formulas! I clicked on a cell containing a CUBEVALUE() formula and was greeted by multiple rows of slicer names in the function bar. Here are two tricks to keep your formulas easy to maintain and update! Cube Formula Trick 1: Consolidate Your Slicer References! Cube formulas are worksheet functions, so they bring their ‘worksheet function drama’ with them (lack of portability, unique formulas, individual updates, etc). The flexibility of cube formulas is powerful, but it does carry a price. (Imagine if you could use them in Power BI Designer!) They’re a big part of what makes Excel simply the world’s best data tool, period. Cube Formulas: Flexible and PowerfulĬube formulas allow you to add PowerPivot/SSAS Tabular calculations to any cell in virtually any orientation that you can think of. These two tricks freed up time that I used to drive further improvements and start performing real analysis, rather than just maintaining a report. This particular scorecard was still under development, and maintaining and making changes to it had become a full-time job! All of the individual cube formulas needed to be updated several times a week, and this was expected to go on for months as executives made up their minds on the final product.įortunately, I found two tricks that allowed me to:Ī.) Change slicer references in all cube formulas with a single clickī.) Modify entire tables across multiple sheets in seconds Cube Formulas: Ultimate flexibility for your reports but lots of drama to update…Ī while back, I was tasked with finding a way to automate upkeep of a scorecard built primarily with thousands of cube formulas (Yes, thousands!). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |