I have a table of titles with an individual record for each ISBN, so there can be up to four records in total for a given book. Paper or hardcover binding, and a PDF or reflowable text electronic versions. Common fields include the title itself, as well as two subject codes. The definitions for these are in a separate table, and I am able to create a dropdown filter which gives a combined code and description for selection, "Art - Art History," for example.
Since the subjects draw from a common table, I'd like to effectively merge the selection into one choice, so that the user can select from either one to get a selection, considering that Metabase by default will use AND logic to narrow the focus when using multiple filters. Doing so would allow the user to select all the art books, whether art is in the code position 1 or 2. Playing around with the idea, I can create an SQL table consisting of the ISBN and subject code, then reference that as a join to allow the full combination of the code and description for the dropdown filter.
So far so good. But, (and you knew there had to be one for me to make this post ), the problem comes in when I want to show the totals only for all of a set of books, inclusive of all the subjects. This additional file causes the books that have two subjects to be counted twice, effectively "double dipping" the totals. It works fine if choosing individual subjects.
It is if I need Metabase to use one question if this filter isn't being used, and another with this additional table included in the question if the filter is selected with one or more values. On the same dashboard in the same space, ideally.
Something on the order of if filter choices from subjects filter is null (or none), then execute the question that doesn't use the additional table, otherwise use it. This would have to kick in when the filter is selected by the user so Metabase can create the drop-down list.
I have played around also with simply concatenating the two fields, but then the drop-down list functionality gets lost and it presumes that the user will know what they are looking for, which might not be the case.
Any thoughts welcomed.