Ravindra BagaleCourses & study guides

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

21.3 The Older Method: Disconnected Table + SWITCH

By Last updated:

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?