How can I create an option to filter out NULL values in a field?

user_2593068 0
Viewed 833

I have used a Google Sheet to create a data source for a dashboard in Google Data Studio:

Account_Number Process_Date Business_Unit Budget_Reference Vender_Name
1000001 7/1/2017 113111 0 ABCD Plumbing
1000002 7/9/2017 114122 0 ACME-1 Electric
1000003 6/14/2017 114223 1
1000004 5/11/2017 112444 1 Shark Industries
1000005 5/12/2017 113334 2 Cyberdyne Systems
1000006 5/11/2017 114440 2 Ollivander's Wand Shop
1000007 5/9/2017 120001 2
1000008 5/17/2017 120009 2 Wayne Enterprises
1000009 4/4/2017 120005 3 Fun City - USA
1000010 4/15/2017 120014 3
1000011 3/11/2017 120111 3

I used it to build a table and now want to use an advanced filter control to filter out rows containing blank vendor names:

Account_Number Process_Date Business_Unit Budget_Reference Vender_Name
1000001 7/1/2017 113111 0 ABCD Plumbing
1000002 7/9/2017 114122 0 ACME-1 Electric
1000004 5/11/2017 112444 1 Shark Industries
1000005 5/12/2017 113334 2 Cyberdyne Systems
1000006 5/11/2017 114440 2 Ollivander's Wand Shop
1000008 5/17/2017 120009 2 Wayne Enterprises
1000009 4/4/2017 120005 3 Fun City - USA

Google Data Studio Report:

current

google-data-studio
1 Answers

Use either approach (1 or 2), based on the requirement:

  1. Flexible - Checkbox: Provides the option for users to switch NULL values on or off
  2. Fixed - Filter: Use if the aim is to hide NULL values

1) Flexible: Control Box

To create a toggleable feature, a checkbox control can be used, with the control field:

IF(Vendor_Name IS NULL, FALSE, TRUE)

The calculated field above uses the IF function to detect NULL values in the Vendor_Name field, which then filters data based on the checkbox selection:

  • ⊟: (Default) all data
  • ☑: Data excluding NULL values
  • ▢: Only NULL values

To view all data, click the ↶ Reset button on the report header. Optionally, use report links to ensure that users start with a default view:

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate:

gif

2) Fixed: Filter

To hide NULL values from users (without the ability for users to toggle), this filter would do:

filter_exclude_null

Exclude Vendor_Name is NULL

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to demonstrate:

gif_v2

user_128925530
Related