Listing zero values in a question

I have been fiddling with the query editor and visualisation settings and am pretty sure this can't be done. Just posting it here to make sure.

The database has a list of articles and their sources. The goal is to find out if there aren't any new records for a given source in the last 7 days, for example.

Unfiltered, it looks like the table below or as a chart listing all the sources.

And this is the query.

To make this complete the goal, every source should be listed even if it didn't have new articles added in the last 7 days.

Am I right to conclude that once I apply a filter there is no way to show the full list of sources?

Alternatively, is it possible to have a table showing only Sources whose value for article count is zero?

Hi @brunoamaral
If you have an Articles.ID column, then instead of using Count, then use "Number of distinct values" of that column. If you cannot make it work like that, then use SQL instead, so you have better control.

Thank you @flamber, that does work but breaks, expectedly, when I add the filter for the last 7 days. I'll look into building it as a SQL Query like you suggest.

@brunoamaral What breaks? It sounds like you want to create a separate question, which only filters Articles the last 7 days, then you create a question with Source as based joining in the Article question.

Looking back I wasn't clear about what I am trying to do.

My system collects data from several sources, sometimes one of them breaks. I need to a way of knowing if a certain source hasn't been giving back results in the last few days.

The current solution shows the ones that return results, but excludes sources with a count of zero.

Did you end up finding a solution for this @brunoamaral ? I've been banging my head on this one also!

This can be done with the query builder and no SQL queries. You need 2 things.
1 - a list of all the types in the data
2 - a count of each type for the period of interest

If you don't have (1) as a separate lookup table, you can create it as a subquery, in Metabase that's a saved question.

Create another subquery for (2), make it a saved question in Metabase.

Create a new question, starting from saved question (2). Join saved question (1), make sure it's a right outer join, this means you'll get a row in the result for every row in (1). You can start the query from either dataset, just change the join type as appropriate.

The resulting data has every type, and a count if there are records in the period of interest.

Personally I think it would be better if Metabase would let you build these subqueries explicitly in the query builder without having to create yet another saved query, but we use the tools we've got.

I'm either missing a step or what I'm after is just enough different that I can't make it work. Here is my situation. I basically have a table of items that people complete (Table 1). I want to track their month over month progress. So I created Table 2 as a temp table that takes the current date and gives you the first of that month, then counts back X number of months (Table 2). So in this example, I'm looking at the past 4 months and want to see every single month listed, whether it's zero or not, and the count be when the Resolution Date (by Month) equals the Calendar Date (by Month).

So Desired Outcome count would be for everyone in Table 1. Then if I filtered to a specific Assignee, I could get the 2nd Desired Outcome. My hope is for this format so I can visualize it as a month over month trend or bar chart.