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?