How to build a slicer to filter location in both table

[Anonymous]

I've made some simplified tables to show how my data is setup and what my problem is:

I have 3 basic tables - the Bridge table is derived from the Dent Table. 

I want to filter by "Canada" and see all results from both tables - but if I use the slicer from either table I get only partial results in the other table. 

This is the result I would want to see using one common slicer:

Is this possible? please help

[PhilipTreacy]

@Anonymous 

Download PBIX file with the example shown below

Create a table called locations that stores the locations names i.e.

Your tables are then related like this

Create a slicer from the Locations table and when you filter you get this

Regards

Phil

[Anonymous]

Thanks for your reply - I guess I should have been more clear that I need the dent and events tables to be related on events via the bridging table. So I should still get this table when I add Dents.dentID to a table with Events.evenID 

In your model I can't even put items from both datasets into the same table

[Anonymous · Accepted solution]

Thanks for the reply from @PhilipTreacy  , please allow me to provide another insight.
Hi @Anonymous ,

1. Create a Location table.

2.Create two measures.

FilteredCount_Dent = 
CALCULATE(
    COUNTROWS('Dent'),
    FILTER(
        'Dent',
        'Dent'[location] IN VALUES('Location'[location])
    )
)

FilteredCount_Event = 
CALCULATE(
    COUNTROWS('Event'),
    FILTER(
        'Event',
        'Event'[location] IN VALUES('Location'[location])
    )
)

3. After placing these two measures into the corresponding table visuals, the three tables can then be filtered simultaneously by the slicer built from the Location table.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you!!! This was so helpful. Is there a way to hide this column in the table and still have it filter? 

[Anonymous]

Hi @Anonymous ,

Go to Table Visual-> Column headers->Text->Turn off text wrap.

Move the mouse cursor into the column header of the column you want to hide.

Slowly move the mouse to the right until it becomes 2 diverging arrows.

Click and drag to the left until the column disappears

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.