Apply a filter across mutliple tables

[GaryH]

Hi All,

I have a faily standard sales model developed that has customer, product, sales rep etc dims, and lastly a "region" dimension.

The company is seperated into regions, and for each region there is a unique set of customers,products etc.

I am trying to work out a way that when a region is selected all the customers,products and other dimensions will be filtered by this region.

I can't use "both" ways cross filters, as I have multiple facts which makes this ambigious, and I also don't want the behaviour of filtering to only what's in the fact tables.

I also can't use a one-way relationship, as the region table need to link to the fact, and this also causes ambigious paths.

Does anyone know of a technique that when one or more regions are selected (via a slicer or filter) that filter gets applied to all the dimensions, so customer where region_id = "North", material where region_id = "North", sales_rep where region_ID = "North".

Thanks,

edit: Here is a simple model that shows what I need to do.  Basically filter all tables by region when one or more regions are selected.

[SivaMani]

@GaryH,

Can you share your model diagram for better understanding?

[GaryH]

Here is a small sample of what I'm trying to do.  So basically when a "region" North is selected, I need th region="North" applied to all the other dimensions and facts.

[SivaMani]

@GaryH,

Add the dimensions to the fact table (Sales) with the below DAX script and use them in the visuals,

E.g.,

Sales Rep_Related = RELATED('Sales Rep'[Sales Rep])

Hope it will help you.

[GaryH]

Thanks for your response, however I'm not sure that this will work, as this is just going to lookup on an existing relationship, where it's being abel to create all the relationships that is the key constraint.

[SivaMani]

Okay. Let's try this.

1. Create a count measure in the fact. E.g. Count_M = COUNT(Sales[SalesId])
2. Add this measure as a visual level filter in the slicers and apply Count >= 1

Please mark it as the solution if this works. Appreciate your kudos.

[GaryH]

Thanks once again Siva. 

I did come across this as a solution if I were using slicers, however my reports are using "all pages filters" as there are far too many filter possibilities for slicers to work.   Unfortunately, unlike slicers, filters don't have a "vlsual level filter".  So, this isn't going to work for me.

[SivaMani]

In that case, I don't think of any other solutions except RELATED. RELATED should work for your model (assuming based on the screenshot you posted).