Display & filter column headers

papelr

I am trying to display this Google Sheet table in a filterable table in Google Looker:

It's easy enough to bring it in, so that it looks like this:

Every column header has to be brought in as a metric, which works, but I'd like to have two filters at the top (drop-downs) where I can pare down the table to just one column/one row (e.g., Banquet Beer in the row and Jim Kirk in the column). Is there a way to do this, or am I stuck bringing the columns as metrics? Not sure how else to display this data in a useable manner, thank you!

sam8

You're right that bringing in each column as a metric is going to be a problem. There's no way to remove/add columns using filter controls.

So, if this has a chance of working, you'll need to use dimensions for both the column and row header. Could you make a copy of the Athlete field, and then make a pivot table on that? That way, you'd be able to set up the two filters as desired.

papelr

Thank you! Are you saying make a separate pivot table in my Google Sheets backend, or go with the pivot table within Looker, as you linked in your reply? I'm thinking you are referring to a possibly nested data table, with the blended data, like you can achieve within a Power BI table. Appreciate it!

sam8

Oh, I wasn't even thinking of changing the Google Sheets backend. You could define another field in Looker Studio, which also references the Athlete field, and then use that to make a pivot table in Looker Studio.

You may be on to something though! If my simple suggestion doesn't work, it might be easier to make the pivot table in Google Sheets so that you can bring it over more 1-1 into Looker Studio.

papelr

I've been beating my brains out over this, to be honest. I don't know if there's a way in the free version of Looker. Maybe I'm looking for more of a "transpose" idea. I will say putting all of the column headers in the Dimension category made the table at least filterable by Athlete, even though I'd love to get a column header filter going, too. Sigh. Thank you!!

The dashboard view is here: https://lookerstudio.google.com/u/0/reporting/eaa804c3-0906-4f3f-827f-314a9b0ad5bb/page/p_y4d2ahgy5c...

Can also send the Sheet!