Conditionally format a dimension, based on another dimension that's not included in the report

[MichaelP]

Hey, newbie to Looker Studio here.

I'd like to conditionally format a dimension, based on the value of another dimension that I don't want to display in the report.

See specific example below. I'm using the value in the "Since Last Review" dimension of "NEW" to highlight the "Festival" value in red. But I don't want to display the "Since Last Review" dimension in the report.

If I remove "Since Last Review" from the dimensions displayed in the report, it's then not available to my conditional formatting configuration.

So is there a way to include but hide the "Since Last Review" dimension from my report so that I can use it for conditional formatting of the "Festival" dimension?  Or is there another way of achieving the same thing that I'm not aware of?

Thanks in advance

[sam8]

Very cool use case. I don't think this is possible though - I don't know of a way to hide the dimension without removing it. 

I recommend creating a feature request! https://support.google.com/looker-studio/answer/13715345

[MichaelP]

I've done as you suggest Sam  https://issuetracker.google.com/issues/361603697

[lauratilton · Accepted solution]

Here's my hack for this:
Create a calculated field like this:

IF(Since Last Review = "New", CONCAT(" ",Festival), Festival)

You can then use this as your first field in the table. Any that are "New" will have a space at the front of the value (that was concat-ed in) and you can conditionally format that column to be red if the value starts with a space. The spaces aren't visible, so it doesn't look any different. (You could also mark it with an asterisk or something more visual if you wanted)

I use quite often when I want to conditionally format values in the table based on a value I don't want to include in the table. (If you do this with CASE instead of IF, you can concatenate in different numbers of spaces for different values, and conditionally format as desired...again, the spaces don't show but the conditional formatting will pick up on them.)

[sam8]

Genius!

[MichaelP]

Thanks Laura. I've currently got any Dimensions I need, but don't want to display, squashed into minimum width columns at the far right of the table.  Not a very elegant solution at all! 

Ok, I've implemented that in a copy of my report.....

IF(Since Last Review in ("NEW", "UPDATED"),CONCAT(" ", Festival),Festival)

... and left the "Since Last Review" dimension in the report just to make sure everything works as expected - and the conditional formatting worked using the leading space. 

But once I removed the "Since Last Review" dimension from the report, it stopped working. So it still seems to depend on the inclusion of the dimension.

[lauratilton]

So strange. I use this hack all the time so the dimension definitely doesn't need to be included. Try it with an asterisk instead and see if you can conditionally format it that way.

[MichaelP]

Hey Laura, it works!   I created a brand new simple report and tried your hack there, and got it to work no problem - so there must be another dependency going on in my main report that I'll have to go and find now.  Thanks for your help.  

[tommdza]

HI Laura, I have a question, how adapt this solution when I need conditionally format a metric and this format depend by other metric hidden?  
Thanks in advance

[lauratilton]

You can definitely use this approach with metrics too, but you'd end up transforming the metrics in your table to dimensions (since they will be numbers with spaces or asterisks in front of them which turns the field into a string/dimension.

To use another metric (not in the table) as the criteria, you'd just set up your IF (or CASE) to use <, >, = to determine your indicators for the formatting. 

Something like this? (although you might need to CAST the Value2 as text, you'd have to play around with it.)

IF(Value1 > 0, CONCAT(" ",Value2), Value2)