Ravindra BagaleCourses & study guides मराठी Track your progress

21. Dynamic Charts: Field Parameters, What-if and Dynamic Top N

21.3 The Older Method: Disconnected Table + SWITCH

By Last updated:

Start with a problem

Shahrukh opens an old report built before field parameters existed. It uses a table of metric names and a SWITCH measure.

Explain simply

The older method: a disconnected table (no relationships) holds metric names; a SWITCH measure reads the slicer choice and returns the matching measure.

Show the data

Metric (disconnected table) Order
Sales 1
Orders 2

About this data: This table uses columns: Metric (disconnected table), Order. This table uses columns: Slicer, Pune, Nashik, Kolhapur. This table uses columns: Field parameters, Disconnected table + SWITCH. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

  1. Home › Enter data › table Metric List with Metric = Sales, Orders.
  2. Create Selected Metric = SWITCH(SELECTEDVALUE('Metric List'[Metric]), "Sales", [Total Sales], "Orders", [Total Orders], [Total Sales]).
  3. Slicer on Metric List[Metric]; bar chart City × Selected Metric.
  4. Choose Orders.

Show the result

Your result should look like this (fictional sample):

Slicer Pune Nashik Kolhapur
Sales 850 800 450
Orders 3 3 2

Explain what happened

The slicer filters only the disconnected table; SELECTEDVALUE reads it and SWITCH picks the measure.

Explore further

  • Format strings do not change automatically – a field parameter handles that better.

Detailed reference for this lesson:

Before field parameters, developers used a table that is not related to anything, plus a SWITCH measure. It is still useful when you need custom logic per option.

Steps in Power BI

  1. Home › Enter data › table Metric List with columns Metric (Sales, Orders, AOV, Avg Delivery) and Sort (1–4) › Load. Do not create relationships.
  2. Select Metric › Column tools › Sort by column › Sort.
  3. Create the measure below and add a slicer on 'Metric List'[Metric] (single select).
  4. Use [Selected Metric Value] on the chart's Y-axis and [Selected Metric Title] as the dynamic title (fx).
Selected Metric Value =
SWITCH(
    SELECTEDVALUE('Metric List'[Metric], "Sales"),
    "Sales",        [Total Sales],
    "Orders",       [Total Orders],
    "AOV",          [AOV],
    "Avg Delivery", [Avg Delivery Time (mins)]
)

Selected Metric Title = SELECTEDVALUE('Metric List'[Metric], "Sales") & " by City"
Field parameters Disconnected table + SWITCH
Setup Dialog, no DAX needed Table + measure
Switch dimensions Yes Hard (needs extra tricks)
Formatting Each measure keeps its own format One measure = one format (use a dynamic format string to fix)
Custom logic per option Limited Full control (e.g. different filters per option)

Ravindra Bagale's Tip

Friends, many students add a new option to the disconnected table and forget to add it to the SWITCH measure, so it shows blank. Keep the table and the SWITCH in sync, and give SWITCH a sensible default value for "no selection". Clear?

Fix common mistakes

Wrong result Reason Correction
Always shows Sales Fallback used because nothing selected Single select with a default
Orders shown as ₹3 One format for the SWITCH measure Use dynamic format strings or field parameters
Slicer filters the chart's cities Relationship created by mistake Keep the table disconnected

Practise independently

Practice task

What does Selected Metric return with no selection?

Hints
  • Last argument of SWITCH.
Solution

Total Sales (the default) – ₹2,100 overall.

Think like a professional

You will maintain old reports. Recognising this pattern and migrating it to field parameters is useful on day one of a job.

Ravi Sir's Cards for this lesson

The older method: disconnected table + SWITCH. Older files and some special cases still use this method.Revision card

मराठी cards