Hi Everyone
I have been able to use conditional formatting using CASE statements to work around the maximum of 20 rules in Looker Studio. The rules are based on student percentile levels for each student year level from 2022-2026. Year 2 percentiles have a different threshold to Year 3 etc.
The issue is I have had to include dimensions in the table using calculated fields which are then included in the conditional formatting process. I have minimised the width of each of the dimensions but you can still see 5 columns of 1 character width in the table. I guess I would like them not to be there or be invisible. I hope this makes sense.
Thank you for your assistance.
Continuing the discussion from Unable to filter Null value entered:
Dear WK
Thank you for your help and the link as well. Luckily I was able to adapt Lauratilton’s solution, ‘Facing issues with Report Filter’ which was similar to the filter issue like mine. It appears to have worked whereby a null input by a user no longer displays all results and the unique code only displays their child’s results.
Thank you again for your help.
Kind regards
Ian
Nice workaround on the 20-rule limit — using CASE-based calculated fields to drive the formatting is exactly the right trick. The visible “helper” columns are a known annoyance in Looker Studio because, unlike Looker (LookML) where you can set `hidden: yes`, a table chart in Looker Studio will render every dimension you add to it.
A few ways to deal with it, roughly best to worst:
**1. Move the helper fields out of “Dimensions” and into the style/formatting only.** In many cases you don’t actually need the helper dimension as a column — conditional formatting in Looker Studio can reference a field via “Color by” → a specific field without that field being a displayed dimension. If your formatting rule lets you pick “Color by: ”, set the rule’s color source to your CASE field and then remove that field from the table’s Dimensions list entirely. That removes the column while keeping the rule working.
**2. If the field must stay in the table, hide the column visually.** Looker Studio has no true “hide column” toggle, so the common approaches are: drag the helper columns to the far right, shrink them to minimum width (which you’ve done), and then set their text color equal to the cell background so the single character disappears. You can also cover them by overlaying a shape/rectangle matching the table background on the report canvas — crude but effective for a fixed-width table.
**3. Best long-term fix: push the logic upstream.** Instead of CASE calculated fields in the report, create the percentile-band field in your data source (a calculated field at the *data source* level, or in the SQL/custom query / view feeding the report). A data-source calculated field can be used by conditional formatting without ever being added as a visible table column, which sidesteps the whole problem. If you control the underlying table/query, doing the `CASE WHEN year_level = 2 … END` there is the cleanest.
Option 1 or 3 will actually eliminate the columns rather than just hiding them, so I’d try those first. If you can share whether your conditional-formatting dialog gives you a “Color by → field” picker, I can be more specific about which applies to your setup.