Display All Locations with Zero Failure Count on Selected Date

[AugustoMoro]

Hi everyone,

I'm working with a dataset in Looker Studio where I need to display all locations, even those with zero failures, based on a selected date. Here's a simplified version of my data:

Date Location Failure Count

16/07/2024	Location 1	1
16/07/2024	Location 2	1
16/07/2024	Location 3	1
17/07/2024	Location 1	1
17/07/2024	Location 3	1

I created a date filter to select "17/07/2024". However, I can't get all three locations (Location 1, 2, and 3) to display. I want Location 2 to be shown with a Failure Count of 0.

I've tried creating calculated fields such as:

• IFNULL = IFNULL(Failure Count, 0)
• NARY_MAX = NARY_MAX(Failure Count, 0)

But neither of these approaches worked.

How can I use Looker Studio features to achieve this? Any help or guidance would be greatly appreciated!

Thank you!

[Francesco_V]

If you have access to the underlying data source and you can create a custom SQL, you should be able to create a query that generates all dates in the period and locations, and adds the fact table to that combinations when there is a match (outer).
If you analyze your fact table directly, Studio would not be able to fill the gaps