Advanced filter options from different source.

[farina_dj]

Hi Community, good day,

I'm a bit stuck in a solution I need to produce for an odd situation.

I'd like to ask your help to see if it's possible what I'm trying to do, please.

My main table is a list of Events, similar to this:

Event	Customer	Severity
x1	c1	Info
x2	c1	Warning
x3	c2	Critical
x4	c3	Info

Now I need to add the information Areas where the event happened because the user wants to filter by them. It's in a new table, registered like this:

ID	Event	Area
1	x1	Zone 1
2	x1	Zone 2
3	x2	Zone 1
4	x3	Zone 1

As you can see the relationship between Events and Areas will be 1 - N. So if I just do a direct join (or merge) it will bring duplicate records for events. That is exactly what I need to prevent, but I also need to add the filter by Areas in the dashboard.

I did try to concatenate them first to have a 1-1 relationship but the filter becomes like:

Which is not good, because these values can vary among many types of areas and happen simultaneously in many. We could have for example |Zone B, Floor 2|, |Zone B, Floor 4, Zone 1|, etc.

Although we have the Advance Filter, using the option "contain" and then filtering by a term, the users don't necessarily know all the names of the areas that it can be to insert the correct term.

So the question is. Is there programmability that makes a filter give the options from a different source table (for example with all the areas) and applies it against the main table with a "contains" clause? Or if there is another solution that I'm not seeing, please.

I really appreciate the attention, thanks.

[Greg_Deckler]

@farina_dj Can you just have 2 slicers, one for Event and one for Area?

[farina_dj]

Hi @Greg_Deckler , thanks for the answer. 
I might be missing something. A slicer needs a field to filter right? But as the relation is 1 - N, it's duplicating the events. If I go for making the relationship 1-1 by concatenating the areas in a event, I have that ugly filter like:

[Greg_Deckler]

@farina_dj I'm not understanding something. If the relationship is Events 1:N Areas and you create a slicer based on the Event column in Events and create a slicer based on the Area column of Areas then when you select an Event in that slicer then the Area slicer will only display areas for that Event. 

[farina_dj]

Hi @Greg_Deckler , sorry, no I didn't create a slicer for Event. Events is the table I'm analysing. I want to see events for the Customer X, events of the country Y, events with critical severity, events in the area Zone 1, etc. Events is the main table, EventID would be de PrimaryKey.

[Greg_Deckler]

@farina_dj Is your relationship bi-directional?

[farina_dj]

@Greg_Deckler, No It's one EventID that can have many Areas related so a 1-N in the relationship between Event-Area.

Events has many others relationships with other tables, but just 1-1, only in this case of Area that the same EventID can happen in more than one Area.

Area has just this relation with Events.

[Greg_Deckler]

@farina_dj Can you make it bi-directional or no? That would solve the issue so that Areas can filter Events

[farina_dj]

@Greg_Deckler , sorry I think I didn't understand before, The table Areas does filter the Events, yes.
If I do a regular relation between Events and Areas I does work, and the filter get like this: 

And it filters by Areas. If I select Zone 2 it shows me only events of the Zone 2.

But the problem is the events that happens in Zone 1 and Zone 2 are being counted twice. Which means that if I don't apply any filter, the number of events is wrong, because the relation with Areas brings a cartesian plane.
That's the why I tried to produce another table AreasConcatenated that groups the same EventID and concatenates the areas to have a 1-1 relation. So the row becomes EventID = "x1", Area = "Zone 1, Zone 2".
But then it brakes the filter, because it becomes: 

What I'm trying to reach is a filter that only shows one option for each Area, but in a way that doesn't duplicate the event records if I don't filter.