Dynamic filtering of two tables based on one slicer

[v-tangjie-msft]

Scenario:

In this article, we'd like to achieve a scenario that distributes a table's data into two visuals for displaying while still filtering them with a single slicer.

The detailed requirements are: Create a report with two table visuals and one slicer visual. Two table visuals are created with the same table fields but the data that showed is different, e.g. each table visual could only display half of the data. And when we select items in the slicer, two table visuals will render the data according to the selections.

Expected Result :

When the slicer has no value selected：

When the slicer has value selected:

Sample Data:  

Table:

Table2:

Create with below DAX formula:

Table 2 = VALUES('Table'[Name])

How: 

1.Create a measure with below DAX formula:

Measure =
VAR tmp =
    VALUES ( 'Table 2'[Name] )
VAR cur_name =
    SELECTEDVALUE ( 'Table'[Name] )
RETURN
    IF ( cur_name IN tmp, 1 )
    ) 

2.Add two table visuals with the same table fields and add above measure to both visual's filter pane. Filter table visuals that Table1 visual only shows name with A,B,C,D and Table2 visual only shows name with E,F,G,H.

3.Add a slicer visual with Table2's Name field, cancel the interaction between two table visuals.

In this way, we can distribute a table's data into two table visuals but still filter them with a single slicer. Hope this article helps everyone with similar questions.  

Author: Binbin Yu

Reviewer: Ula and Kerry

Dynamic Filtering of Two tables based on One slicer, multiple selected values.pbix