Filtering by Parameter across two identical tables?

[SevsBo]

I have two identical tables, let's call them Table1 and Table2, that have Date1, Date2, and Value1, Value2, as the points of interest. The tables are linked by a common date filter, so that Table1 is linked on Date1 and shows Value1 and Table2 is linked on Date2 to show Value1 for a different time range set on the same time slicer. So far no mention of Value2.

On a separate page I am using a Parameter1 with Table1 where I can toggle between Value1 and Value2 within the same date filtering. Now I am wondering if I build Parameter2 in Table2, is there a way I can filter parameters from both tables at once, as they both have to be on the same page?

In other words, I want to change the data on the page from showing:

- Table1, Date1, Value1 AND Table2, Date2, Value1

to showing:

- Table1, Date1, Value2 AND Table2, Date2, Value2

with a single filter linking both?

[Angith_Nair · Accepted solution]

Hi @SevsBo 

To synchronize toggling between Value1 and Value2 for both Table1 and Table2 using a single slicer, you can use a Disconnected Table approach which is mentioned below:

1. Create a Parameter Table for Toggle Options:
In Power BI, create a new table called ParameterTable with options Value1 and Value2.
Go to Modeling > New Table and add:

ParameterTable = DATATABLE(
    "Toggle", STRING,
    { {"Value1"}, {"Value2"} }
)

2. Create a Measure for Each Table:
For Table1 and Table2, create a measure that checks the toggle selection from ParameterTable and returns either Value1 or Value2 based on that.
In Table1, add this measure:

SelectedValue_Table1 = 
IF(
    SELECTEDVALUE(ParameterTable[Toggle]) = "Value1",
    SUM(Table1[Value1]),
    SUM(Table1[Value2])
)

In Table2, add this measure:

SelectedValue_Table2 = 
IF(
    SELECTEDVALUE(ParameterTable[Toggle]) = "Value1",
    SUM(Table2[Value1]),
    SUM(Table2[Value2])
)

3. Use the Parameter as a Slicer:
Place ParameterTable[Toggle] as a slicer on your report page.
Select SelectedValue_Table1 and SelectedValue_Table2 as values for your visuals instead of the original columns.

Now, when you change the slicer between Value1 and Value2, it will simultaneously update both Table1 and Table2 to display the selected value across their respective date filters.

[SevsBo]

Thank you @Angith_Nair, that's amazing!

I am getting a small but consistent discrepancy between the results produced this way and with the Parameters within each Table, if I use them as filters. I think I need to review my filters on the visuals but overall this approach worked!

[ThomasWeppler]

I would combine your two tables to one table in m.

Go to home and press this buttom.

Choose two tables and pick the secound table you want to add together.

Now you have one table with all the data and it will be much easier to work with. 

I hope this will help you solve your problem 🙂

[SevsBo]

Unfortunately I have to keep them separate - I have to use one time slicer to filter across two different Date columns in the same table, that is why I split it into two in the first place. One date is start and the other is end date, and we want to show both values at once with one time slicer.