Your CFO walks into a meeting and asks a question that sounds simple: "What happens to our gross margin if raw material costs increase by 15%?" You open Power BI, stare at your beautifully crafted report, and realize that the model is a perfect snapshot of historical reality — but it cannot answer a hypothetical. The data is what it is. You can slice it, dice it, drill through it, but you cannot change it without going back to the source. This is the wall that nearly every Power BI developer hits eventually, and it is more frustrating because Power BI does not natively support writeback — the ability to have users input values that feed back into the model's calculations.
The good news is that DAX, combined with a few clever modeling patterns, can simulate a remarkably convincing version of writeback. You can give users slicers that feel like input controls, and the entire report — KPIs, charts, tables, forecasts — can respond dynamically to those inputs as if the underlying data had actually changed. The techniques involve parameter tables, disconnected slicers, and DAX measures that branch their logic based on slicer selections rather than raw data. Done well, these patterns are production-grade tools that you will reach for again and again.
By the end of this lesson, you will understand not just how to wire up a what-if parameter but why the pattern works at the engine level, where it breaks down under pressure, and how to build multi-dimensional scenario models that go well beyond the toy examples in most documentation.
What you'll learn:
This lesson assumes you are comfortable with the following:
CALCULATE does to the filter contextSUMX operateYou do not need prior experience with what-if parameters, but you should be an advanced DAX practitioner. We will be moving quickly through concepts that would otherwise take their own full lessons.
Before building anything, you need a precise mental model of the constraint. Power BI's in-memory engine, VertiPaq, loads data into columnar storage at refresh time. During a user session, the data is read-only. Measures compute dynamically against that static data, but there is no mechanism for a user interaction — a slicer selection, a button click — to modify a stored value in VertiPaq. The data is immutable between refreshes.
This is not a bug; it is a deliberate architectural choice. VertiPaq's extraordinary compression and scan speeds depend on the data being static. Writeback would require row-level locking, transaction management, and cache invalidation — the kinds of mechanisms that belong to OLTP databases, not columnar analytical engines.
What is dynamic in a Power BI session is the filter context. Every visual, every measure evaluation, happens inside a filter context that can be shaped by slicers, page filters, report filters, and even other measures. The key insight behind all writeback simulation patterns is this: a slicer selection is not writing data — it is modifying filter context, and a well-written measure can treat that filter context as if it were input data.
This distinction is everything. Once you internalize it, the patterns that follow will feel inevitable rather than clever.
Power BI Desktop includes a "What if parameter" feature under the Modeling tab. When you use it, it generates a calculated table like this:
Price Adjustment =
GENERATESERIES( -0.30, 0.30, 0.01 )
It also generates a measure:
Price Adjustment Value =
SELECTEDVALUE( 'Price Adjustment'[Price Adjustment], 0 )
And it adds a slicer to the report canvas pointed at that table. This is convenient, and for simple single-axis scenarios it is perfectly fine. But it has a significant limitation: the wizard creates a connected table by default in recent versions, and even when disconnected, it only gives you one dimension to play with at a time without additional configuration. More critically, many developers use this wizard without understanding what it is actually doing, which leads to subtle errors when scenarios become more complex.
Let us build a richer version by hand. Suppose you are modeling a retail business and want to let users simulate changes across three dimensions simultaneously:
For each parameter, create a calculated table:
Revenue Growth Parameter =
GENERATESERIES( -0.20, 0.30, 0.01 )
Rename the default column name. In Power BI, right-click the column in the Fields pane after creating the table, and rename [Value] to [Revenue Growth Rate]. Then:
COGS Pct Parameter =
GENERATESERIES( 0.40, 0.70, 0.01 )
Rename the column to [COGS Pct]. And:
OpEx Multiplier Parameter =
GENERATESERIES( 0.80, 1.40, 0.05 )
Rename the column to [OpEx Multiplier].
Critically important: Do not create any relationships between these tables and the rest of your model. They must remain completely isolated — disconnected — from your fact tables and dimension tables. This is what makes them parameter tables rather than ordinary dimension tables.
Why disconnected? If you accidentally create a relationship between a parameter table and a fact table, the slicer selections on the parameter will filter the fact data directly, which is exactly what you do not want. You want the parameter value to be read by a measure, not to filter rows. Keeping the table disconnected ensures that only measures can access the selected value, through
SELECTEDVALUEorVALUES.
Now create the companion measures:
Selected Revenue Growth =
SELECTEDVALUE( 'Revenue Growth Parameter'[Revenue Growth Rate], 0 )
Selected COGS Pct =
SELECTEDVALUE( 'COGS Pct Parameter'[COGS Pct], 0.55 )
Selected OpEx Multiplier =
SELECTEDVALUE( 'OpEx Multiplier Parameter'[OpEx Multiplier], 1.0 )
The second argument to SELECTEDVALUE is the default — what the measure returns when no value is selected, or when multiple values are selected. Choosing sensible defaults is important for user experience. A revenue growth rate of 0 means "no change." A COGS percentage of 55% might represent your current actual ratio. An OpEx multiplier of 1.0 means "hold steady."
Now we have the parameter infrastructure. The real craft is in writing measures that consume these parameters correctly.
Assume your model has a Sales fact table with columns Revenue, COGS, and Date. Your base measures might look like this:
Actual Revenue =
SUM( Sales[Revenue] )
Actual COGS =
SUM( Sales[COGS] )
Actual Gross Profit =
[Actual Revenue] - [Actual COGS]
Actual Gross Margin % =
DIVIDE( [Actual Gross Profit], [Actual Revenue] )
Actual OpEx =
SUM( Sales[OpEx] )
Actual Operating Income =
[Actual Gross Profit] - [Actual OpEx]
Now build the scenario versions. These measures read the parameter selections and apply them to the actual data:
Simulated Revenue =
[Actual Revenue] * ( 1 + [Selected Revenue Growth] )
This is simple and readable. When the slicer is set to +15%, Selected Revenue Growth returns 0.15, and the simulated revenue is 115% of actual.
Simulated COGS =
[Simulated Revenue] * [Selected COGS Pct]
Notice what we did here: we applied the COGS percentage to the simulated revenue, not the actual revenue. This reflects the real-world logic — COGS moves with revenue, and the user is specifying what percentage of that simulated revenue is COGS. This kind of chain dependency is easy to get wrong if you are not thinking carefully about the causal structure.
Simulated Gross Profit =
[Simulated Revenue] - [Simulated COGS]
Simulated Gross Margin % =
DIVIDE( [Simulated Gross Profit], [Simulated Revenue] )
Simulated OpEx =
[Actual OpEx] * [Selected OpEx Multiplier]
Simulated Operating Income =
[Simulated Gross Profit] - [Simulated OpEx]
The really useful outputs for scenario analysis are the variances:
Revenue Variance (Scenario vs Actual) =
[Simulated Revenue] - [Actual Revenue]
Revenue Variance % =
DIVIDE(
[Revenue Variance (Scenario vs Actual)],
[Actual Revenue]
)
Operating Income Variance =
[Simulated Operating Income] - [Actual Operating Income]
Gross Margin Impact (pp) =
[Simulated Gross Margin %] - [Actual Gross Margin %]
The gross margin impact in percentage points is particularly useful for communicating the effect to non-technical stakeholders. A CFO understands "our gross margin drops 4.2 points" more immediately than a variance in absolute dollars.
Design principle: Keep your simulated measures cleanly separated from your actual measures in the Fields pane by using a dedicated display folder. In the measure properties, set the display folder to "Scenario Analysis" for all simulated measures. This prevents report authors from accidentally using simulation measures where actuals are intended.
Let us go deeper into why this works, because the mechanism is not always intuitive, and understanding it will help you debug issues when they arise.
When a user selects a value on a slicer connected to Revenue Growth Parameter, Power BI adds a filter to the query context: Revenue Growth Parameter[Revenue Growth Rate] = 0.15. This filter applies to the Revenue Growth Parameter table.
Because there is no relationship between Revenue Growth Parameter and any other table, this filter does not propagate anywhere. The Sales table is completely unaffected. When a visual renders and calls [Simulated Revenue], the filter context that reaches the Sales table is determined only by the visual's own axes (date, product, region, etc.) and any report/page/visual-level filters. The Revenue Growth Parameter filter does not touch Sales.
When [Simulated Revenue] executes [Actual Revenue] * (1 + [Selected Revenue Growth]), the SELECTEDVALUE( 'Revenue Growth Parameter'[Revenue Growth Rate], 0 ) call explicitly reaches into the filtered state of the Revenue Growth Parameter table. At that point, exactly one value is in the filter context for that column — 0.15 — so SELECTEDVALUE returns 0.15.
The multiplication then happens in scalar context. The result is returned to the visual.
This is a classic example of measures acting as "bridges" between disconnected parts of the model. The measure is the only thing that connects the parameter selection to the actual data computation. There is no relationship doing any filtering; it is all explicit measure logic.
If the user clears the slicer or the parameter slicer is not on the page at all, Revenue Growth Parameter[Revenue Growth Rate] has its full set of values in the filter context — every row in the table from -0.20 to 0.30. SELECTEDVALUE detects multiple values and returns its alternative result: 0. So [Simulated Revenue] returns [Actual Revenue] * 1.0 — exactly the actual revenue. This is graceful degradation: the simulation "turns off" when no scenario is selected.
Warning: This graceful degradation behavior can be a double-edged sword. If a user forgets to set the slicer and compares "Simulated Revenue" to "Actual Revenue," the numbers will match and it may look like the scenario has no effect, rather than that no scenario was selected. Consider adding a measure that signals the active scenario state:
Scenario Is Active = IF( ISFILTERED( 'Revenue Growth Parameter'[Revenue Growth Rate] ) || ISFILTERED( 'COGS Pct Parameter'[COGS Pct] ) || ISFILTERED( 'OpEx Multiplier Parameter'[OpEx Multiplier] ), "Scenario Active", "Showing Actuals (No Scenario Selected)" )Display this as a card visual at the top of the scenario page.
Single-slicer what-if is useful, but the most powerful scenarios come from comparing multiple hypotheses simultaneously. Imagine your executive team wants to see three scenarios side by side: a pessimistic case, a base case, and an optimistic case — each with different combinations of growth rate, COGS, and OpEx assumptions.
This requires a fundamentally different architecture.
Create a static table — either in Power Query or as a DAX calculated table — that defines named scenarios with specific parameter values:
Scenario Definitions =
DATATABLE(
"Scenario Name", STRING,
"Revenue Growth Rate", DOUBLE,
"COGS Pct", DOUBLE,
"OpEx Multiplier", DOUBLE,
{
{ "Pessimistic", -0.10, 0.65, 1.20 },
{ "Base Case", 0.05, 0.55, 1.00 },
{ "Optimistic", 0.20, 0.48, 0.90 }
}
)
This table is also disconnected from the rest of the model. Add a slicer pointed at Scenario Definitions[Scenario Name].
Now rewrite the parameter-reading measures to pull from this table instead of (or in addition to) the individual parameter slicers:
Active Revenue Growth =
VAR SelectedScenario =
SELECTEDVALUE( 'Scenario Definitions'[Scenario Name] )
VAR ScenarioGrowth =
CALCULATE(
SELECTEDVALUE( 'Scenario Definitions'[Revenue Growth Rate] ),
'Scenario Definitions'[Scenario Name] = SelectedScenario
)
VAR ManualOverride =
SELECTEDVALUE( 'Revenue Growth Parameter'[Revenue Growth Rate] )
RETURN
COALESCE( ManualOverride, ScenarioGrowth, 0 )
Wait — there is a problem here. CALCULATE inside a measure that already has filter context on Scenario Definitions is redundant and could cause confusion. Let us think more carefully.
When the user selects "Pessimistic" in the scenario slicer, the filter context already restricts Scenario Definitions to that one row. So SELECTEDVALUE( 'Scenario Definitions'[Revenue Growth Rate] ) will correctly return -0.10. We do not need the inner CALCULATE at all:
Active Revenue Growth =
VAR ScenarioGrowth =
SELECTEDVALUE( 'Scenario Definitions'[Revenue Growth Rate] )
VAR ManualGrowth =
SELECTEDVALUE( 'Revenue Growth Parameter'[Revenue Growth Rate] )
RETURN
COALESCE( ManualGrowth, ScenarioGrowth, 0 )
The COALESCE creates a priority chain: if the user has explicitly selected a manual override on the Revenue Growth Parameter slicer, that takes precedence. If they have selected a named scenario, that applies. If neither, default to zero. This is a flexible design that gives power users the ability to "tweak" predefined scenarios without destroying the named scenario framework.
Apply the same pattern to the other parameters:
Active COGS Pct =
VAR ScenarioCOGS =
SELECTEDVALUE( 'Scenario Definitions'[COGS Pct] )
VAR ManualCOGS =
SELECTEDVALUE( 'COGS Pct Parameter'[COGS Pct] )
RETURN
COALESCE( ManualCOGS, ScenarioCOGS, 0.55 )
Active OpEx Multiplier =
VAR ScenarioOpEx =
SELECTEDVALUE( 'Scenario Definitions'[OpEx Multiplier] )
VAR ManualOpEx =
SELECTEDVALUE( 'OpEx Multiplier Parameter'[OpEx Multiplier] )
RETURN
COALESCE( ManualOpEx, ScenarioOpEx, 1.0 )
Now rebuild the simulated measures to use the Active parameters:
Simulated Revenue v2 =
[Actual Revenue] * ( 1 + [Active Revenue Growth] )
Simulated COGS v2 =
[Simulated Revenue v2] * [Active COGS Pct]
Simulated Gross Profit v2 =
[Simulated Revenue v2] - [Simulated COGS v2]
Simulated OpEx v2 =
[Actual OpEx] * [Active OpEx Multiplier]
Simulated Operating Income v2 =
[Simulated Gross Profit v2] - [Simulated OpEx v2]
The architecture above still only shows one scenario at a time (whichever named scenario is selected). To show all three simultaneously in a single visual — say, a clustered bar chart with one cluster per scenario — you need a different technique.
Place Scenario Definitions[Scenario Name] on the X-axis of the chart, and use the simulated measure. Because the chart will iterate over each scenario name and apply it as a filter context, the measure will see only one row of Scenario Definitions at a time, giving you the correct scenario parameters for each bar automatically.
This is one of those moments where Power BI's visualization engine does the heavy lifting for free, as long as your measure logic is sound. The chart literally evaluates [Simulated Operating Income v2] three times — once for each scenario — and each evaluation gets a different filter context on Scenario Definitions, so SELECTEDVALUE returns different values each time.
Gotcha: If you also have the individual parameter slicers on the same page, a user might select a manual override that changes all scenarios simultaneously (because
COALESCEgives manual overrides priority). This can be confusing. Consider using page-level filters or bookmarks to separate the "named scenario comparison" view from the "custom scenario tuning" view.
Real-world scenarios rarely have perfectly independent parameters. Suppose your business model has the following constraint: when revenue grows by more than 10%, supply chain costs (part of COGS) increase by an additional 3% due to capacity strain. This is a non-linear interdependency, and it is the kind of detail that makes the difference between a toy model and a model an executive will trust.
Effective COGS Pct =
VAR BaseCoGSPct = [Active COGS Pct]
VAR GrowthRate = [Active Revenue Growth]
VAR CapacityStrainAdjustment =
IF( GrowthRate > 0.10, 0.03, 0 )
RETURN
BaseCoGSPct + CapacityStrainAdjustment
Now replace [Active COGS Pct] with [Effective COGS Pct] in your Simulated COGS v2 measure:
Simulated COGS v2 =
[Simulated Revenue v2] * [Effective COGS Pct]
The user sees the right output without needing to understand the adjustment mechanism. But you should document this logic — consider a tooltip measure or a "model assumptions" page that explains all adjustments.
In more complex models, you might need to propagate adjustments through several layers. A useful pattern is to compute all effective parameters in a single VAR block within the final output measure:
Simulated Operating Income (Full Model) =
VAR GrowthRate = [Active Revenue Growth]
VAR BaseCoGSPct = [Active COGS Pct]
VAR OpExMult = [Active OpEx Multiplier]
-- Interdependency: capacity strain when growth > 10%
VAR CapacityAdj = IF( GrowthRate > 0.10, 0.03, 0 )
VAR EffCoGSPct = BaseCoGSPct + CapacityAdj
-- Interdependency: hiring freeze when OpEx multiplier < 0.90 reduces fixed costs
VAR HiringFreezeAdj = IF( OpExMult < 0.90, -0.02, 0 )
VAR EffOpExMult = OpExMult + HiringFreezeAdj
-- Compute the P&L
VAR SimRevenue = [Actual Revenue] * ( 1 + GrowthRate )
VAR SimCOGS = SimRevenue * EffCoGSPct
VAR SimGP = SimRevenue - SimCOGS
VAR SimOpEx = [Actual OpEx] * EffOpExMult
RETURN
SimGP - SimOpEx
Computing everything in one measure with VAR chains is both more readable and more performant than calling six separate sub-measures. Each sub-measure call would require a new measure evaluation context, whereas VAR values are computed once and reused.
Another powerful application of this pattern is projecting future periods. Rather than simulating a change to historical data, you want to extend the time series forward using scenario assumptions.
In Power Query, create a table of future months:
let
StartDate = #date(2025, 1, 1),
MonthCount = 24,
DateList = List.Dates(StartDate, MonthCount * 30, #duration(1, 0, 0, 0)),
MonthStarts = List.Distinct(
List.Transform(DateList, each Date.StartOfMonth(_))
),
FilteredMonths = List.FirstN(MonthStarts, MonthCount),
ToTable = Table.FromList(
FilteredMonths,
Splitter.SplitByNothing(),
{"Projection Date"}
),
TypedTable = Table.TransformColumnTypes(
ToTable,
{{"Projection Date", type date}}
)
in
TypedTable
This table is kept disconnected from your Date dimension. Add a relationship if you want the date axis to include both historical and projected periods; otherwise use it as its own axis in a dedicated projection chart.
Projected Revenue =
VAR GrowthRate = [Active Revenue Growth]
VAR BaseRevenue =
CALCULATE(
[Actual Revenue],
DATESINPERIOD(
'Date'[Date],
MAX( 'Date'[Date] ),
-12,
MONTH
)
)
VAR MonthsFromBase =
DATEDIFF(
DATE( 2024, 12, 31 ),
MAX( 'Projection Date'[Projection Date] ),
MONTH
)
VAR AnnualGrowthFactor = 1 + GrowthRate
VAR MonthlyGrowthFactor = POWER( AnnualGrowthFactor, 1/12 )
RETURN
( BaseRevenue / 12 ) * POWER( MonthlyGrowthFactor, MonthsFromBase )
This computes a compound monthly growth projection from a trailing 12-month base. The base revenue is divided by 12 to get a monthly run rate, then compounded forward. The POWER function handles the math of converting an annual rate to a monthly compounding rate.
Warning:
POWER( AnnualGrowthFactor, 1/12 )with DAX's integer division will give you the wrong answer. In DAX,1/12evaluates to0because both operands are integers. Write it asPOWER( AnnualGrowthFactor, 1.0/12 )orPOWER( AnnualGrowthFactor, DIVIDE(1, 12) ).
One of the most consequential architectural decisions in scenario modeling is whether your simulations run against aggregated measures or row-level data. Most of the patterns above apply adjustments to aggregated totals. This is computationally efficient and works well when the adjustment is uniform across all rows.
But what if your adjustment should vary by product category? For example, raw material costs increase 15% for Category A but only 5% for Category B.
Create a parameter table with category-level granularity:
Category COGS Adjustment =
DATATABLE(
"Category", STRING,
"COGS Adjustment Pct", DOUBLE,
{
{ "Electronics", 0.15 },
{ "Apparel", 0.05 },
{ "Home Goods", 0.10 }
}
)
Now — do you create a relationship between this table and your Products dimension? That depends on your requirements.
Option A: Create a relationship. If Products[Category] relates to Category COGS Adjustment[Category], then when a visual is broken out by category, the adjustment percentage filters automatically to the correct value. The measure becomes:
Simulated COGS (By Category) =
VAR CategoryAdj = SELECTEDVALUE( 'Category COGS Adjustment'[COGS Adjustment Pct], 0 )
RETURN
[Actual COGS] * ( 1 + CategoryAdj )
This is clean and elegant. But it means the adjustment table is connected, and you lose the flexibility of having a global override.
Option B: Keep it disconnected, use LOOKUPVALUE. If you want the adjustment table to remain outside the relationship graph:
Simulated COGS (By Category, Disconnected) =
VAR CurrentCategory = SELECTEDVALUE( Products[Category] )
VAR CategoryAdj =
LOOKUPVALUE(
'Category COGS Adjustment'[COGS Adjustment Pct],
'Category COGS Adjustment'[Category],
CurrentCategory,
0
)
RETURN
[Actual COGS] * ( 1 + CategoryAdj )
LOOKUPVALUE performs an explicit lookup without using the relationship graph. It is less efficient than a relationship-based filter, but it gives you full control and keeps the parameter table disconnected.
When to use which: Use Option A (relationship) when the category-level adjustments are fixed and you want the visual context to drive the lookup automatically. Use Option B (LOOKUPVALUE) when you need to combine category-level adjustments with user-selected global overrides, or when the relationship would create ambiguity in the model.
For truly complex non-uniform adjustments — where the adjustment varies by individual row, not just by category — you need to iterate over the fact table:
Simulated COGS (Row Level) =
SUMX(
Sales,
VAR RowCategory = RELATED( Products[Category] )
VAR RowAdj =
LOOKUPVALUE(
'Category COGS Adjustment'[COGS Adjustment Pct],
'Category COGS Adjustment'[Category],
RowCategory,
0
)
RETURN
Sales[COGS] * ( 1 + RowAdj )
)
This is significantly more expensive than the aggregation-first approach. SUMX iterates row by row over the filtered Sales table, calling RELATED and LOOKUPVALUE for each row. For a Sales table with tens of millions of rows, this can be prohibitively slow.
Performance rule: Always prefer aggregated simulations over row-level iterations unless you have a specific business reason that aggregation cannot satisfy. Aggregate first, then adjust. The only time row-level iteration is necessary is when the adjustment function is non-linear at the row level in a way that does not commute with aggregation — for example, if the adjustment depends on the individual row's value relative to some threshold.
This exercise builds a complete three-scenario P&L simulation for a fictional SaaS company. Work through it in Power BI Desktop with a fresh file.
In Power Query, create the following table manually (enter data via the "Enter Data" dialog):
| Month | MRR | COGS | HeadcountCost | OtherOpEx |
|---|---|---|---|---|
| 2024-01 | 850000 | 127500 | 320000 | 45000 |
| 2024-02 | 878000 | 131700 | 320000 | 47000 |
| 2024-03 | 902000 | 135300 | 335000 | 48000 |
| 2024-04 | 935000 | 140250 | 335000 | 51000 |
| 2024-05 | 961000 | 144150 | 352000 | 49000 |
| 2024-06 | 990000 | 148500 | 352000 | 53000 |
Name this table SaaS_Actuals. Format the Month column as Date.
In DAX, create these calculated tables:
MRR Growth Parameter =
GENERATESERIES( -0.20, 0.40, 0.01 )
Rename the column to [MRR Growth Rate].
COGS Ratio Parameter =
GENERATESERIES( 0.10, 0.25, 0.005 )
Rename to [COGS Ratio].
Headcount Multiplier Parameter =
GENERATESERIES( 0.70, 1.50, 0.05 )
Rename to [HC Multiplier].
SaaS Scenarios =
DATATABLE(
"Scenario", STRING,
"MRR Growth", DOUBLE,
"COGS Ratio", DOUBLE,
"HC Multiplier", DOUBLE,
{
{ "Bear Case", -0.05, 0.22, 1.15 },
{ "Base Case", 0.12, 0.17, 1.00 },
{ "Bull Case", 0.25, 0.14, 0.95 }
}
)
Verify in the Model view that none of these tables have relationships to SaaS_Actuals.
Actual MRR = SUM( SaaS_Actuals[MRR] )
Actual COGS = SUM( SaaS_Actuals[COGS] )
Actual HC Cost = SUM( SaaS_Actuals[HeadcountCost] )
Actual Other OpEx = SUM( SaaS_Actuals[OtherOpEx] )
Actual Gross Profit = [Actual MRR] - [Actual COGS]
Actual Operating Income = [Actual Gross Profit] - [Actual HC Cost] - [Actual Other OpEx]
Active MRR Growth =
COALESCE(
SELECTEDVALUE( 'MRR Growth Parameter'[MRR Growth Rate] ),
SELECTEDVALUE( 'SaaS Scenarios'[MRR Growth] ),
0.12
)
Active COGS Ratio =
COALESCE(
SELECTEDVALUE( 'COGS Ratio Parameter'[COGS Ratio] ),
SELECTEDVALUE( 'SaaS Scenarios'[COGS Ratio] ),
0.17
)
Active HC Multiplier =
COALESCE(
SELECTEDVALUE( 'Headcount Multiplier Parameter'[HC Multiplier] ),
SELECTEDVALUE( 'SaaS Scenarios'[HC Multiplier] ),
1.00
)
Simulated MRR = [Actual MRR] * ( 1 + [Active MRR Growth] )
Simulated COGS = [Simulated MRR] * [Active COGS Ratio]
Simulated Gross Profit = [Simulated MRR] - [Simulated COGS]
Simulated HC Cost = [Actual HC Cost] * [Active HC Multiplier]
Simulated Operating Income = [Simulated Gross Profit] - [Simulated HC Cost] - [Actual Other OpEx]
OI Variance = [Simulated Operating Income] - [Actual Operating Income]
OI Variance % =
DIVIDE( [OI Variance], ABS( [Actual Operating Income] ) )
Simulated Gross Margin % =
DIVIDE( [Simulated Gross Profit], [Simulated MRR] )
Create a report page with the following layout:
SaaS Scenarios[Scenario]. Set it to single-select.[Simulated MRR], [Simulated Gross Margin %], [Simulated Operating Income], and [OI Variance %].[Actual MRR] and [Simulated MRR] as two lines.SaaS Scenarios[Scenario] on X-axis; [Simulated Operating Income] as the bar. This shows all three scenarios simultaneously.Test the following behaviors:
COALESCE ordering).Wait — that last point reveals an important subtlety. When the clustered bar chart evaluates [Active MRR Growth] for "Bear Case," the filter context includes SaaS Scenarios[Scenario] = "Bear Case". But if the user has also selected a value in the manual parameter slicer, SELECTEDVALUE( 'MRR Growth Parameter'[MRR Growth Rate] ) will still return a non-null value because that slicer is active at the report level. The COALESCE will give the manual value priority — meaning the clustered bar chart will show the same adjusted bar for all three scenarios, which is wrong.
This is the key reason to separate the "named scenario comparison" view from the "custom parameter tuning" view. Use bookmarks to show/hide the appropriate slicers for each mode, or put them on separate pages.
Symptom: The slicer selection filters out rows in your charts — you see fewer data points when you move the slicer. Or your actual measures also change when you adjust a parameter.
Cause: A relationship exists between the parameter table and a fact or dimension table, so slicer selections propagate as row filters.
Fix: Go to Model view and delete any relationships involving your parameter tables. Verify there are no hidden auto-detected relationships (check "Manage relationships" in the Modeling tab).
Symptom: Your simulated measures all show BLANK when no scenario is selected.
Cause: You wrote SELECTEDVALUE( Column ) without a second argument, so it returns BLANK when multiple values are selected.
Fix: Always provide a meaningful default: SELECTEDVALUE( Column, DefaultValue ).
Symptom: The priority chain between manual parameters and named scenarios does not behave correctly — the named scenario always wins, or the manual override is ignored.
Cause: COALESCE takes the first non-BLANK argument. But SELECTEDVALUE returns your specified default (not BLANK) when no value is selected. If your manual parameter default is 0 and no value is selected, COALESCE treats 0 as a valid (non-BLANK) value and stops there — it never reaches the scenario value.
Fix: Use IF( ISFILTERED( Column ), SELECTEDVALUE( Column ), BLANK() ) instead of relying on the SELECTEDVALUE default for the COALESCE chain:
Active MRR Growth =
VAR ManualSelection =
IF(
ISFILTERED( 'MRR Growth Parameter'[MRR Growth Rate] ),
SELECTEDVALUE( 'MRR Growth Parameter'[MRR Growth Rate] ),
BLANK()
)
VAR ScenarioSelection =
IF(
ISFILTERED( 'SaaS Scenarios'[Scenario] ),
SELECTEDVALUE( 'SaaS Scenarios'[MRR Growth] ),
BLANK()
)
RETURN
COALESCE( ManualSelection, ScenarioSelection, 0.12 )
Symptom: Reports that use SUMX for simulated measures take 10–30 seconds to render.
Cause: SUMX iterates every row in the filtered fact table, calling LOOKUPVALUE or RELATED for each one.
Fix: Restructure the measure to aggregate first, then adjust. If the adjustment is uniform per category, compute the category-level adjustment outside the SUMX, or use a relationship and aggregation-based approach.
Symptom: Monthly projections are flat instead of growing.
Cause: POWER( Factor, 1/12 ) evaluates 1/12 as integer division = 0, so POWER( Factor, 0 ) = 1 always.
Fix: Write POWER( Factor, DIVIDE(1, 12) ) or POWER( Factor, 0.08333 ).
Symptom: The "all scenarios" comparison bar chart shows identical bars when a manual parameter is selected.
Cause: The manual parameter slicer applies at the report or page level, overriding the scenario-specific values.
Fix: Use the ISFILTERED-based COALESCE pattern above, and place the manual parameter slicers in a separate bookmark/page. Alternatively, use visual-level filters to exclude the manual parameter table from affecting the comparison chart.
As your scenario models grow in complexity, watch for these performance traps:
Measure chaining depth: Every measure that calls another measure adds evaluation overhead. A chain of eight measures, each calling the next, can cause the engine to re-evaluate filter context at each step. Consolidate complex chains into a single measure with VAR statements. The engine can optimize VAR computations far more effectively than measure call chains.
GENERATESERIES cardinality: A parameter table with 61 rows (e.g., -30% to +30% in 1% steps) is trivial. But if you accidentally create a cross-join between three parameter tables with 61, 61, and 13 rows each, you get a 48,529-row table — and if you then put all three parameters on a single "scenario configuration" table axis, rendering performance will degrade significantly. Keep parameters in separate disconnected tables.
LOOKUPVALUE vs. relationship performance: A relationship-based lookup that Power BI can resolve through the VertiPaq dictionary engine is orders of magnitude faster than a LOOKUPVALUE call, which executes as a scan. If a category-level adjustment table is stable (not user-modifiable), model it with a relationship.
DirectQuery compatibility: None of these patterns work in DirectQuery mode. Parameter tables are always imported (they are calculated tables or manually entered tables), and the measures reference them through in-memory evaluation. If your fact data is in DirectQuery, consider using Composite Models — keep the fact table in DirectQuery and the parameter/dimension tables as imported. The simulation measures will run against imported parameter values and push only the necessary aggregated queries to the DirectQuery source.
You have built something genuinely sophisticated here. The core insight — that a disconnected table's filter context can be read by measures without affecting the rest of the model — unlocks an entire class of interactive analytics that most Power BI users do not know is possible. Let us recap the key concepts:
The fundamental mechanism: Disconnected parameter tables + SELECTEDVALUE + ISFILTERED allow measures to read user selections as scalar inputs without modifying any underlying data.
The architecture: Parameter tables feed "Active Parameter" measures, which feed simulated measures, which feed variance measures. Each layer is cleanly separated and independently testable.
The named scenario pattern: A DATATABLE-based scenario definitions table lets users choose from predefined hypothesis bundles while still allowing power users to override individual parameters.
The interdependency pattern: Computed adjustments within a single measure's VAR chain allow non-linear, conditionally applied relationships between parameters.
The performance principle: Aggregate first, adjust after. Avoid row-level iteration unless the business logic absolutely requires it.
If this lesson has left you wanting more, the logical next topics are:
The gap between "showing historical data" and "answering hypothetical questions" is where Power BI's analytical value multiplies. You now have the foundation to build that bridge.