How to Use a Disconnected Table in Power BI?

In Power BI, a disconnected table is a powerful tool that can enhance your data models by enabling dynamic and complex interactions without direct relationships with other tables. It’s particularly useful for scenarios like parameter selection or what-if analyses. Here’s a step-by-step guide on how to use a disconnected table in Power BI, complete with an engaging example.

Step 1: Create a Disconnected Table

First, you need to create a table that isn’t linked to any other data in your model. This can be done directly in Power BI.

Example: Suppose you want to allow users to select a sales multiplier to see how changes might affect total sales.

1. Go to the “Modeling” tab.
2. Click on “New Table”.
3. Enter the following DAX formula to create a table with multipliers:

Multiplier Table = DATATABLE("Multiplier", DOUBLE, {{"1.0"}, {"1.1"}, {"1.2"}, {"1.3"}, {"1.5"}})

1. This formula creates a table named Multiplier Table with a single column Multiplier and several multiplier options.

Step 2: Create Measures Using the Disconnected Table

Now that you have a disconnected table, you can create measures that reference the selected value in this table.

Example: Create a measure to calculate adjusted sales based on the selected multiplier.

1. Go to the “Modeling” tab.
2. Click on “New Measure”.
3. Enter the following DAX formula:

Adjusted Sales = SUM(Sales[Amount]) * SELECTEDVALUE(Multiplier Table[Multiplier], 1)

1. This measure multiplies the total sales by the selected multiplier from the Multiplier Table. If no multiplier is selected, it defaults to 1 (no change).

Step 3: Use the Disconnected Table in a Slicer

To let users interact with the multiplier, you’ll use the disconnected table in a slicer, which allows them to select a multiplier and see its impact dynamically.

1. Click on the report canvas.
2. In the “Visualizations” pane, select the “Slicer” visual.
3. Drag the Multiplier field from the Multiplier Table into the slicer.

Step 4: Observe the Changes

Now, as users select different values from the slicer, they will see the Adjusted Sales measure update accordingly in your report visuals. You can use other visuals like bar charts or KPIs to display the adjusted sales next to actual sales for comparison.

Additional Tips

• Dynamic Titles: Create dynamic titles that reflect the selected multiplier by using a measure that includes SELECTEDVALUE(Multiplier Table[Multiplier], "None selected") to show which multiplier is currently selected.
• Multiple Parameters: You can extend this by adding more parameters to the Multiplier Table or creating additional disconnected tables for different scenarios.

Conclusion

Using a disconnected table in Power BI lets you build dynamic and interactive reports that can accommodate complex scenarios and what-if analyses without altering your underlying data model. It’s a neat trick to make your reports more interactive and engaging for end-users, enhancing their decision-making process with real-time, customizable data views.