Using a disconnected table in Power BI provides flexibility and dynamic interaction between numerous visuals without having to modify any part of the underlying data model or affecting the relationships among the different tables. A disconnected table, commonly referred to as a parameter or slicer table, is not related to any other table in the model. Still, it enables a DAX-related interaction.
Use it as a disconnected table to control the filter selections or slicer options over various visuals, as follows:
1. Create a Disconnected Table.
First, create a disconnected table.
One of the most frequent uses of a disconnected table is to create a parameter table for controlling slicers or filters. Still, it is not connected to any other tables in the data model.
DAX Example of a parameter table:
ParameterTable =
DATATABLE("Parameter", STRING, {{"Option 1"}, {"Option 2"}, {"Option 3"}})
The simple columns in this table may have a few options. Other than that, it has no association with any other tables in the model.
2. Utilize the Disconnected Table in the Slicer
Add the Parameter column from the disconnected table to the Slicer visual.
This Slicer allows the user to select one of the different options in the disconnected table.
3. Use DAX to Control Interactions Based on the Slicer Selection Directly
Now, you can create DAX measures responding to that slicer selection and thus controlling dynamically displayed values in other visuals.
Example: Dynamic measures based on a slicer selection. Say you wanted to show sales data according to the selected option from the disconnected table. You would use SELECTEDVALUE to extract the slicer value and proceed with the calculations accordingly.
Dynamic Sales Measure =
SWITCH(
SELECTEDVALUE('ParameterTable'[Parameter]),
"Option 1", CALCULATE(SUM('Sales'[SalesAmount]), 'Sales'[Region] = "North"),
"Option 2", CALCULATE(SUM('Sales'[SalesAmount]), 'Sales'[Region] = "South"),
"Option 3", CALCULATE(SUM('Sales'[SalesAmount]), 'Sales'[Region] = "East"),
SUM('Sales'[SalesAmount]) -- Default case if no option is selected
)
In this example:
-
SELECTEDVALUE('ParameterTable'[Parameter]): This retrieves the selected option from the disconnected table.
-
SWITCH: Based on the selected option, it adjusts the calculation of the SUM('Sales'[SalesAmount]) with filters applied based on the option chosen (e.g., region-specific sales).
4. Apply the Measure Across Multiple Visuals
Now that you have a dynamic measure, you can use it in multiple visuals (e.g., bar charts, line charts, tables) to show the relevant data based on the slicer selection. The slicer from the disconnected table drives the filtering logic, but it does not directly affect any relationships in the data model.
Example:
-
Place the Dynamic Sales Measure in a bar chart to display sales by region.
-
Add the ParameterTable slicer to filter the chart dynamically based on the selected option.
-
You can use the same measure in multiple visuals (tables, KPI cards, etc.) to create consistent interaction across the report.
5. Ensure Flexibility and Scalability
To ensure that the interaction logic remains flexible and scalable:
-
You can extend the disconnected table by adding more options and logic to the DAX measure. For instance, if you want to add more regions, time periods, or categories, simply update the ParameterTable and modify the DAX logic accordingly.
-
You can create more complex measures using similar logic and keep them scalable by using DAX functions like SWITCH, IF, or FILTER to respond to different slicer selections.
Example: Multiple Disconnected Tables Driving Interactions
You can use multiple disconnected tables to control different aspects of a report. For example, one slicer could control regions, while another could control time periods (e.g., months or quarters).
-
Region Selection:
RegionTable = DATATABLE("Region", STRING, {{"North"}, {"South"}, {"East"}, {"West"}})
2. Time Period Selection:
TimePeriodTable = DATATABLE("Period", STRING, {{"Last 3 Months"}, {"Last 6 Months"}, {"YTD"}, {"Last Year"}})
3. Dynamic Measure Based on Both Slicers:
Sales Measure =
SWITCH(
TRUE(),
SELECTEDVALUE('RegionTable'[Region]) = "North" && SELECTEDVALUE('TimePeriodTable'[Period]) = "Last 3 Months",
CALCULATE(SUM('Sales'[SalesAmount]), 'Sales'[Region] = "North", 'Sales'[Date] >= DATEADD(TODAY(), -3, MONTH)),
SELECTEDVALUE('RegionTable'[Region]) = "South" && SELECTEDVALUE('TimePeriodTable'[Period]) = "Last 6 Months",
CALCULATE(SUM('Sales'[SalesAmount]), 'Sales'[Region] = "South", 'Sales'[Date] >= DATEADD(TODAY(), -6, MONTH)),
SUM('Sales'[SalesAmount]) -- Default case
)
Advantages of Using Stand-Alone Tables:
No Influence on Data Model: The stand-alone table is not connected to another table, so this won't affect your relationships or create unwanted changes in the model.
Flexible Interaction: Independent tables provide the capability of controlling how a report may react without having to redesign your data model.
Scalable: You can easily add coordination in the disconnected table and DAX straight away to support more slicers or parameters for complex interactions.
