'OR' filter set up

#1

I have a situation that I need to implement a filter based on a number of columns, where the value in any combination of them is true.


Here is the basic data layout (sorry if the preformatting didn't work)

ID    A    B    C    D measureValues
 1                            y    n    n    y    ...........
 2    n    n    n    y    ...........
 3    n    y    n    y    ...........
 4    n    n    n    n    ...........

The client wants a multi select filter on the dashboard, based on any combination of ABCD being true. So maybe they will want to see rows where A=true, maybe they want if A or D is true, maybe if any is true etc.


I am not sure how to implement this in the data cube and dashboard though. I have tried a number of ideas with no success.


Separate filter per column: This won't work, as when I filter out the 'n' values in col A, ID 2 won't have the 'y' in col D.


Calculated measure: I can make a calculated measure for 'A or B', and another one for 'A or B or C' etc. and script the correct filter to set on the dashboard, but the number of measures and filters necessary is large, and grows exponentially with the number of columns.


Unpivot: I can unpivot on these columns and then filter. 

ID A B C D filter Value    measureValues
 1 y n n y   A      y   ...........
 1 y n n y   B      n   ...........
 1 y n n y   C      n   ...........
 1 y n n y   D      y   ...........
 2 n n n y   A      n   ...........
 2 n n n y   B      n   ...........
 2 n n n y   C      n   ...........
 2 n n n y   D      n   ...........

The problem is that if 'A or D' is selected, then ID 1 will have 2 rows and ID 2 will only have 1, so the aggregated measure values may be wrong. This may be workable if I calculate percentages based on the number of rows and then use distinct count of ID to get the base sizes.


Is there anything within the datacube that can help, or does anyone have a lateral thinking workaround?


Thanks



#2

Have you thought of doing the SQL query with parameterized where clause then have buttons set those parameters?


I have not yet used parameters in Dundas yet but my first thought was you need a where clause filter then my second was how to do that.



Thinking out loud on how to make that work were you could get all results and also filter.......

case where clause, the case takes the parameter??? that sounds better than two buttons for each column.


a case statement for each column might be better than one big one.


#3

If it is just the case that you have multiple entries of ID values, and therefore duplicates of the same measure value, setting the Aggregator to "Average" in the measure properties on the Metric set will eliminate that. This is different to using the AVG forumula in a Formula measure as it is operating on the data at the Data Source/Data Cube level, and not the data that exists in the Metric set.


  1. [ID1][A=y] 23.4
  2. [ID1] [D=y] 23.4
  3. [ID2][A=y] 34.2

Image title

Measure using Sum aggregator

  1. [ID1] 46.8
  2. [ID2] 34.2

Measure using Average aggregator

  1. [ID1] 46.8 /2 = 23.4
  2. [ID2] 34.2 /1 = 34.2


You can set this to be the default/only supported aggregator in the measure properties in the Data Cube if you don't want to have to fiddle with this on the Metric Set every time. If you need to Sum the totals, you can use a "Totals Calculation Rule formula" to do that. http://www.dundas.com/support/learning/documentation/design-view/using-a-custom-total-calculation

#4

@david.glickman, this is an old post but did you ever get a workable solution to this problem? I have a similar situation. Thanks.

#5

Hi @kelly.lowary

Unfortunately I didn’t. The client changed their mind and didn’t require this in the end, so I didn’t pursue it further.

#6

Ok, thanks for the follow up.

#7

Old post indeed, but as all filters are booleans my take would be to assign a bit to each, eg:
A = bit 0
B = bit 1
C = bit 2 and so on.
Then the combined filter is A * 2^0 + B * d^1 + C * 2^2 = A + B2 + C4 (with true=1 and false=0).
You now have a unique int value for combination, which you can easily match from a dashboard.

#8

Here is how I solved it: My situation was to view all orders that met several OR conditions, for example, WHERE Field1 = x OR Field2 = x or Field3 IN (a, b, c).
I created a new source view in my database that did a union of all values with a result of:

OrderID SearchValue
111111 Field1: x
111111 Field1: y
222222 Field2: a
333333 Field1: q
333333 Field2: b
333333 Field3: y

I created a SearchValue data cube off of this and made a relationship between OrderID to my Orders Data Cube.
I added a filter to my dashboard that connected to view parameter vpSearchValue on a metric set joining SearchValue Data Cube and Orders Data Cube.
The user can now search for any value in the filter and select the options that they want to include and can also see the associated field name in the filter. This is because value X may be in Field1 and Field2 and the user can distinguish between it showing up in either field.
I could also set additional AND filters on this metric set in the dashboard like OrderDate, etc. with other filters.
This is similar to what David Glickman suggested above in his pivot example and per his point, you have to be careful of any aggregations. I want to see the Cost on the Order so I changed the aggregation type on the Cost Measure to be MAX which works for my situation because I am looking at the Order level row anyway.