User Profile
Pvtpyle
Frequent Visitor
Joined 1 year ago
User Widgets
Contributions
The Dyson Sphere Series | Part 1: Dynamic Measures
The Dyson Sphere Series | Part 1: Dynamic Measures The Pain: Measure Explosion Measure explosion is a real bottleneck when building a Power BI report, and the current approaches to solve it are inadequate. Example (5 Metric Report) Metrics: Net Sales, Cost, Margin, Order Count, Margin per Order Calculations needed: Current Year, Prior Year, YoY, YoY% Result: - SUM (5) + Prior Year (5) + YoY (5) + YoY% (5) = 20 measures to write and maintain Current Approaches User Defined Functions (UDFs): Solve authoring speed. They don't solve measure explosion, model bloat, or format string management. Calculation Groups: Come close, but fall short on format string handling. A calc item can only interrogate the base measure via SELECTEDMEASURENAME() - it cannot interpret itself. As a result, all calc items must be hardcoded and iterated on every single calc item. Solution There's a better way. And I'm going to show you how and why it works. One measure. One dispatcher. Every metric, every derived calculation, every visual - all routed through a single engine that responds to a slicer selection automatically. That's Dynamic Measures. Scope Limitation: Dynamic Measures is not built for multi-metric side-by-side layouts. If you need Net Sales, Margin, and Order Count as separate columns in the same table, this isn't the right tool for that. Strength: One metric, full depth. Switch the slicer and every attribute column CY, PY, CY/GT%, YoY(±YoY%) updates across the entire hierarchy simultaneously. The visual below runs entirely off one [Calc] (CY in the table) measure. Prognosis Every base metric you add doesn't cost you 1 measure. It costs you 4. Base Metrics | Traditional | Dynamic | Reduction -------------|--------------|------------|---------- 5 | 20 measures | 8 measures | 60% 15 | 60 measures | 18 measures| 70% The traditional approach requires a SUM, a Prior Year, a YoY, and a YoY% for every single base metric - each one written individually, each one maintained individually. The model doesn't scale, it accumulates. And the cost compounds. At 5 metrics you're writing 20 measures. At 15 you're writing 60. Every new metric request from a stakeholder is a silent tax on your model. Six months from now you'll be afraid to touch anything because you've lost track of what depends on what. Diagnosis A single [Calc] measure reads a slicer selection and routes to the corresponding base metric using SWITCH(SELECTEDVALUE()). [PY], [YoY], and [YoY%] all reference [Calc], not the individual base measures. Switch the slicer - everything updates. Add a new base metric - add one line to the SWITCH. Nothing else changes. Your base SUMs stay; you still need one per metric. But Prior Year, YoY, and YoY% collapse into one measure each, regardless of how many base metrics you have. Three measures do the work of however many your report demands. Treatment Step 1 - Create the Field Parameter Table Create a table named Metric in your model with the following data: Base | Sort -----------|----- NetSls$ | 1 Cost$ | 2 Margin$ | 3 OrderCT | 4 Mgn$PerOrd | 5 The Base column is what the slicer reads and what the dispatcher references. Keep the values consistent with your fact table column names - it reduces confusion when wiring up the SWITCH later. The Sort column controls display order in the slicer. Step 2 - Create the Base Measures One SUM measure per metric. These are the foundation everything else builds on. Adopt a naming convention: prefix each base measure with an underscore. It keeps them visually separated from your derived measures in the field list and signals to anyone reading the model that these are inputs, not outputs. _NetSls$ = SUM(Transactions[NetSls$]) _Cost$ = SUM(Transactions[Cost$]) _Margin$ = SUM(Transactions[Margin$]) _OrderCT = SUM(Transactions[OrderCT]) One important exception - ratio metrics. If a metric is a ratio between two other metrics, don't create a column for it and SUM it. Sum of ratios produces incorrect totals. Instead, derive it by dividing the two SUM measures: _Mgn$PerOrd = DIVIDE([_Margin$], [_OrderCT], 0) This ensures the total row reflects the true weighted ratio - Margin total divided by OrderCT total - rather than a meaningless sum of per-row calculations. Step 3 - Create the Calc Dispatcher This is the engine. SWITCH(SELECTEDVALUE()) reads the active slicer selection from Metric[Base] and routes to the corresponding base measure. Calc = SWITCH( SELECTEDVALUE(Metric[Base], "NetSls$"), "Cost$", [_Cost$], "Margin$", [_Margin$], "OrderCT", [_OrderCT], "Mgn$PerOrd", [_Mgn$PerOrd], "NetSls$", [_NetSls$], [_NetSls$] -- fallback: unmatched selection defaults to Net Sales ) Two things worth noting: The default value in SELECTEDVALUE(Metric[Base], "NetSls$") ensures visuals never open blank. The report always shows something meaningful on load. The final line is a fallback for any unmatched selection. If a Metric[Base] value doesn't match any SWITCH case it defaults to Net Sales rather than returning blank. This is a safety net, not expected behavior - keep your Metric[Base] values and SWITCH keys in sync. Step 4 - Create the Supporting Measures With [Calc] in place, the three supporting measures almost write themselves. Each one references [Calc] directly - switch the slicer, all three update automatically. -- Prior Year: lifts the date filter then shifts context back one year PY = CALCULATE( [Calc], REMOVEFILTERS(Transactions[Date]), SAMEPERIODLASTYEAR(Transactions[Date]) ) -- YoY: returns BLANK instead of zero when either side has no data YoY = IF( NOT(ISBLANK([Calc]) || ISBLANK([PY])), [Calc] - [PY] ) -- YoY%: ABS([PY]) prevents sign flip on negative prior year values YoY% = DIVIDE([YoY], ABS([PY])) Remission That's it. 6 measures regardless of how many base metrics your report carries. Swap the slicer, everything follows. The pattern scales cleanly. Add a new base metric, add one line to the SWITCH, done. No touching PY, YoY, or YoY%. No rebuilding. No maintenance spiral. Below includes both a PBIX template file and a DAX code file. Drop in your own base measures and wire up your own metrics. The dispatcher is ready to go. GitHub: Download Template + DAX Connect on LinkedIn: linkedin.com/in/mkienchauExecutive BI Framework - Demo
Summary: This is my interpretation/design for a business Executive Dashboard with a high degree of focus on usability and accessibility to streamline the visuals and reduce the number of required pages. Pages: Overview: Highlights best and worst performing months Location: Hierarchical location tree with YoY, Dynamic Calculation, Ranking, & Quartile evaluation Customer: Hierarchical customer tree with YoY, Dynamic Calculation, Ranking, & Quartile evaluation Product: Hierarchical product tree with YoY, Dynamic Calculation, Ranking, & Quartile evaluation Historical: Tracks multi-year performance (2020–2024) with CAGR trends, regional and product trajectories, and performance heatmaps. What sets this dashboard apart: 1) Dynamic Calculation slicer (seen at the top left, below page title): This is the primary calculation engine that affect most visuals on each page. Allows users to seamlessly switch between standard business metrics and observe/analyze the impact visually. 2) Nested Slicer Bookmarks (slicer icon at top right): A booklet of slicers organized by categories. Enables the entire suite of slicers for each page while having a minimal impact on overall space. 3) Dynamic Time Intelligence slicer (inside Nested Slicer Bookmarks): Fully integrated time intelligence slicer that affects all visuals of each page, ranging from Trailing 7 days, Trailing 12 months, YTD, Full Year etc. Integration of Dynamic Time Intel in conjunction with Dynamic Calculation provides a level of flexible reporting and analysis that few can replicate. eyJrIjoiNjY2YTllYmItNjczNy00NDRkLTlmOTctNWUzNDgxMTAxOTA4IiwidCI6IjUxZTIwNTFkLWFiM2QtNDk4Mi04MTFiLWQ2ZjUwZjcxMzQyOSIsImMiOjZ9
