What is a Disconnected Parameter Table? and How to Create in Power BI?

What is a Disconnected Parameter Table?

A disconnected parameter table (often created using a “What-If parameter” in Power BI Desktop) is a special kind of table that is not directly related to your main data model tables (like your Sales table). Its primary purpose is to provide a slicer or filter that a user can interact with to select a single value (like ‘N’ in “Top N”). This selected value then acts as an input to your DAX measures.

Because it’s “disconnected,” filtering this table doesn’t automatically filter your main data. Instead, your DAX measure explicitly reads the selected value from this table and uses it to modify its own calculation context.

---

How to Create a Disconnected Parameter Table (What-If Parameter)

The easiest way to create this in Power BI Desktop is by using the New parameter feature.

Here’s how:

1. Go to the Modeling tab: In Power BI Desktop, navigate to the Modeling tab in the ribbon.
2. Click “New parameter”: In the “What-If parameter” section, click on New parameter (it might be labeled as “What if” or “Numeric range”). Self-correction: The “New parameter” button is directly visible in newer Power BI versions. It used to be under “What if” in older versions.
3. Configure the Parameter: A dialog box will appear. Fill in the details:
   • Name: Give it a meaningful name, e.g., “Top N Value”.
   • Data type: Whole number (since ‘N’ should be a whole number).
   • Minimum: 1 (or whatever the lowest sensible ‘N’ is).
   • Maximum: 50 (or a reasonable upper limit for your Top N scenarios).
   • Increment: 1 (this is how much the value changes when using the slider).
   • Add slicer to this page: Check this box. This will automatically create a new table, a measure, and add a slicer to your report page.
   (Example configuration)
   • Name: Top N Value
   • Data type: Whole number
   • Minimum: 1
   • Maximum: 20
   • Increment: 1
   • Default: 5 (optional, but good for initial view)
   • Add slicer to this page: Yes
4. Understand what Power BI creates: When you click “OK,” Power BI does three things:
   • Creates a new table: A table named “Top N Value” (or whatever you named it) will appear in your Fields pane. This table will contain a single column (e.g., Top N Value[Top N Value]) with all the numbers from your specified range (1 to 20 in the example).
   • Creates a measure: A measure is also created in this table, typically named Top N Value Value. This measure simply returns the currently selected value from the slicer. Code snippetTop N Value Value = SELECTEDVALUE('Top N Value'[Top N Value], 5) (The 5 here is the default value if nothing is selected or if multiple items are selected in the slicer).
   • Adds a slicer to your report: A slicer populated with the values from your new parameter table is automatically added to your report canvas.

---

How to Use the Disconnected Parameter Table in Your DAX Measure

Now that you have your Top N Value Value measure (or whatever Power BI named it), you can modify your “Top N Products by Sales” measure to use this dynamic ‘N’.

Original DAX (from Question 3):

Top 5 Products by Sales =
CALCULATE (
    [Total Sales],
    TOPN (
        5, // Hardcoded '5'
        ALL ( Sales[Product Name] ),
        [Total Sales], DESC
    )
)

Modified DAX to use the Disconnected Parameter:

Top N Products by Sales (Dynamic) =
VAR SelectedN = 'Top N Value'[Top N Value Value] // Reference the measure created by the parameter
RETURN
    CALCULATE (
        [Total Sales],
        TOPN (
            SelectedN, // Now uses the dynamic 'N' from the slicer
            ALL ( Sales[Product Name] ),
            [Total Sales], DESC
        )
    )

Purpose of this modification:

By replacing the hardcoded 5 with SelectedN (which reads from your “Top N Value” parameter table), your “Top N Products by Sales (Dynamic)” measure will now react to the user’s selection in the slicer. If the user moves the slider to 10, the measure will calculate the sales for the top 10 products; if they move it to 3, it will calculate for the top 3.

This is a powerful technique for creating flexible and interactive reports! Let me know if you’d like to dive into another advanced DAX concept!