3 Replies Latest reply on Dec 7, 2018 3:46 PM by Vinnie Ahuja

# [10.1] How to filter data with an inclusive "OR" instead of an "AND"?

EDIT: I tried to write my question and problem below, in response to Vinnie

Below is a table of my data (this is merely an example of the actual data, as I at this moment want want to post it online).

 Stop Name Stop ID Bus ID Fares Collected Main St 1 1 10 2nd St 2 1 5 Community College 3 1 30 University 4 1 50 HWY 3 5 2 50 Community College 3 2 100 Costco 6 2 20 Starbucks 7 3 20 I 405 8 3 100 2nd St 2 3 5 3rd St 9 4 0 4th St 10 4 5

What I'd like to do if possible, is to be able to get a total fare amount for any combination of the three Dimensions. For instance, my customer may want to the total fares for all of Bus 1, the fares for Stop ID numbers 2 and 3, as well as for 4th Street.

I have been able to do this (on a workbook not included) if I only filter the fares based off a single dimension.

On the workbook included:

Sheet 1 - Bus ID, Sop ID, and Stop Name (multi-value) filters, but when I select a value combination that doesn't exist (like BUS ID 1, Stop ID 2, Stop Name Community College) I get a Total Fare amount of 0

Sheet 2 - I have some Parameter filters, but I can only select one value of each parameter, where ideally, I'd be able to select multiple extra stops, or buses, etc.

I hope this makes some sense, as I am struggling with putting this into words..

• ###### 1. Re: [10.1] How to filter data with an inclusive "OR" instead of an "AND"?

Hi Tyler,

If I am understanding correctly, a LOD calc should do this for you:

{ FIXED [Bus ID], [Stop ID], [Stop Name]: sum([Fares Collected])}

Cheers

Vinnie

• ###### 2. Re: [10.1] How to filter data with an inclusive "OR" instead of an "AND"?

Hi Vinnie,

Unless I'm misinterpreting your solution, it still won't get me the total fare amount for all of Bus 1, Stops 2/3, and 4th Street.

I'll try to clarify my above statements:

I would like to display the total fare amount for all of Bus 1, Stops 2/3, and 4th Street, via filters/drop-down menus.

So ultimately I can have any combination of buses, stop names/IDs be apart of that display.

The problem being, when if I make a select of two different filters that have no overlap (Bus 1 and Stop 9) the total fare amount I get is zero.

• ###### 3. Re: [10.1] How to filter data with an inclusive "OR" instead of an "AND"?

Hi Tyler -

My head is still spinning a bit but I think I have a better understanding.  I think your solution lies in Level Of Detail Calculations, however rather than one calc as I previously suggested, perhaps you need one for each of these dimensions that only considers that dimension.

{FIXED [Stop ID]: sum([Fares Collected])}

{FIXED [Bus ID]: sum([Fares Collected])}

{FIXED [Stop Name]: sum([Fares Collected])}

The idea is you only want your Bus ID total subject to the Bus ID filter and none of the rest, Stop ID only subject to the Stop ID filter etc.  So if I filter the below for Stop ID or Stop Name, the Bus ID Fare Collected will always be 95 for Bus 1.

You could then add these together in another calc to get a total.

I don't think this is your complete answer, how you design the viz will play a role, but it should get you closer.

Cheers

VInnie