Apply slicer to multiple tables when adding a dim table causes ambiguity

[tamara_nsb]

I have a dim_Location table that and several other tables that are all connected,   many of the table contain the field location but they are not all the same value.    They want to be able to have one "Location" slicer on a page that is applied to all tables but because relationships between the other tables I can't make an active relationship to my dim table or it causes ambiguity.   How can I work around this?

Example.   Customer has a Many-1 relationship with sales.   Which I need to use in several visualizations and measures,  but they also want to be able to filter by location.    Show customers and sales that meet the location slicer.  I know this might not make sense as to "why" I would want to do this but I am trying to make a simple example.

Dim_Location

LocationID	Name
1	AL
2	FL
3	HI

sales

ID	Customer	Location	Value
1	mary	FL	5.00
2	John	HI	2.50
3	sam	HI	7.00

customer

Name	Location
sam	HI
John	FL
mary	AL

[v-bmanikante]

Hi @tamara_nsb ,

Thank you for reaching out to Microsoft Fabric Community forum.

@rajendraongole1 Thank you for your quick response.

You can use the measure suggested by the super user @rajendraongole1 , which works well. Alternatively, you can keep the relationship between the Customer and Location tables inactive and use the USERELATIONSHIP function to control the filter path explicitly.

For example, you can create a measure like this:

CountOfCustomers =

CALCULATE(COUNT(Customer[Name]),USERELATIONSHIP(Customer[Location],Dim_Location[Name]))

)

This allows you to avoid ambiguity while still leveraging both relationships as needed.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[rajendraongole1 · Accepted solution]

Hi @tamara_nsb  - You already have:

Dim_Location[LocationID], Dim_Location[Name]

Sales[Location], Customer[Location]

But cannot connect Dim_Location[Name] actively to both without ambiguity.

This captures the selected location from the slicer

SelectedLocation =
SELECTEDVALUE(Dim_Location[Name])

Create Filtered Measures for Your Visuals

Filtered Sales =
CALCULATE(
SUM(Sales[Value]),
Sales[Location] = [SelectedLocation]
)

And for Customer count

Filtered Customers =
CALCULATE(
DISTINCTCOUNT(Customer[Name]),
Customer[Location] = [SelectedLocation]
)

This works without needing active relationships - the filtering is done within the measure logic based on slicer selection.Only use [Filtered Sales], [Filtered Customers], etc., in your visualizations instead of raw fields. I hope the above steps helps.

[tamara_nsb]

Thanks for the help,  this is exactly what I needed.