I want to suppress rows in my analysis that show zeroes in all the wrong places. In the screenshot of the analysis's table below, I'm referring to rows showing the columns highlighted in red. Ideally, since every row in it contains only zeroes, the entire "Other Revenue" section should collapse.
Each of these three measure columns is a complicated calculation with a FILTER-USING that revolves around a presentation variable populated by a dashboard prompt; the prompt values appear as the month name at the top of each column.
My first obvious thought was to build an OR filter: filter where Aug-22 IS NOT 0, OR Jul-22 IS NOT 0, OR Jun-22 IS NOT 0.
This gives me the desired result of removing rows where all three values are zero, which is good, but has the nasty side effect of displaying a NULL in the rows where only one or two of the values is zero, like this:
So, my next thought was to create a new hidden column that is the sum of the three columns (I also tried throwing in the YTD amount in the rightmost column) and basing the filter on that. Even when I unhid the column and could see it displaying non-null non-zero numbers on the face the of the analysis, any filter I placed on that column treated it as if it were NULL, causing the entire analysis to short-circuit as if all filters had failed.
Finally, I settled on selection steps. Using the hidden column I had developed for the filter before, I tried both excluding the zero records and including only the non-zero records. While this method gave me the desired results for small samples - say, an Income Statement for a single company value - the hit to the performance was unacceptably bad. I went from a sub-30-second runtime to over five minutes on the small sample, and waited over twenty minutes with no results when running the report for a larger population.
Might there be another, more elegant solution to my dilemma?
