Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
TaylorG
Frequent Visitor

How to build a slicer to filter location in both table

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. 

TaylorG_4-1727993862979.png

 

 

TaylorG_3-1727993850628.png

 

 

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

TaylorG_5-1727994316448.png

 

Is this possible? please help

1 ACCEPTED SOLUTION

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

1. Create a Location table.

vdengllimsft_0-1728379316664.png

 

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.

vdengllimsft_1-1728379735660.png


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.

View solution in original post

5 REPLIES 5
PhilipTreacy
Super User
Super User

@TaylorG 

 

Download PBIX file with the example shown below

 

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

 

PhilipTreacy_0-1727997972586.png

 

Your tables are then related like this

 

PhilipTreacy_1-1727998018157.png

 

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

 

PhilipTreacy_2-1727998060523.png

 

 

Regards

 

Phil

 

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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 

TaylorG_0-1728056098314.png

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

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

1. Create a Location table.

vdengllimsft_0-1728379316664.png

 

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.

vdengllimsft_1-1728379735660.png


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.

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

Hi @TaylorG ,

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

vdengllimsft_0-1728460528315.png

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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.