Sorting is disrupted when there is no value after setting filters

[twinsa]

Hi all!

There is a request:

And it works great.
All the values for X are in order from 1 to 12:

However, if you reduce the number of samples in the filters, the values start to disappear after 11:

1. How can I make the filter display 0 when there are no values?
2. How can I make the sort display correctly when one of the values is missing?

{
"browser-info": {
"language": "ru-RU",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/138.0.0.0 Safari/537.36 OPR/122.0.0.0",
"vendor": "Google Inc."
},
"metabase-info": {
"databases": [
"postgres",
"sqlserver"
],
"run-mode": "prod",
"plan-alias": "",
"version": {
"date": "2025-10-21",
"tag": "v0.56.11",
"hash": "01ca4ee"
},
"settings": {
"report-timezone": null
},
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.22 (Debian 12.22-1.pgdg120+1)"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.7.7"
}
}
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "21.0.8+9-LTS",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "21.0.8",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "21.0.8+9-LTS",
"os.name": "Linux",
"os.version": "4.15.0-213-generic",
"user.language": "en",
"user.timezone": "GMT"
}
}

Thanks

[twinsa]

There are also no values for X = 9 and 11

[dwhitemv]

You can set the series to display the value as zero when the value is missing. In the 3-dots menu for the series, there’s a dropbox to set the behavior:

Otherwise, if you want to have all the X values display regardless, you’ll have to generate them in your query, so the X value is output even if no rows match. This comes up with time series a lot, to the point that data warehouses commonly have “calendar tables” or views.

[twinsa]

@dwhitemv

Otherwise, if you want to have all the X values display regardless, you’ll have to generate them in your query, so the X value is output even if no rows match. This comes up with time series a lot, to the point that data warehouses commonly have “calendar tables” or views.

Initially, all values are in the table.
Skips and moving 5 to X appear after disabling values in the dashboard filters

[twinsa]

@dwhitemv

You can set the series to display the value as zero when the value is missing. In the 3-dots menu for the series, there’s a dropbox to set the behavior:

I've already tried to set up missing values as 0

Here's what happens:

With all filters selected: Everything is fine.

I'm reducing the selected values in the filters. Everything is fine for now:

I also remove the values in the filters. And again, everything breaks:

Why is 5 on X at the end of the line instead of in its place?
Why are 9 and 11 missing from X?

[twinsa]

I managed to solve the issue with 5: I changed the sorting within the query. I put the month in the first place. Everything is fine with this.
However, I was unable to configure the gaps (9 and 11) for display.

[dwhitemv]

Try setting the X axis scale to “Linear.”

Otherwise, you need to make sure your query is not outputting any gaps. Metabase can only plot the data you give it.

Adjust your sampling to sample values from every X value, or use a generator function in the query to output all the X values and left join the sampling to it.

EDIT: Consider the following query against the Sample Database. This uses Bernoulli sampling to pick out a random set from the table:

select extract(month from created_at) as monthnum, count(*) as count from orders
where random() > 0.9995
group by monthnum
order by monthnum

This returns a set of order counts for only some months, for example:

 monthnum | count 
----------+-------
        5 |     2
        6 |     2
        8 |     1
        9 |     1
       10 |     1
       12 |     5

Note that not every month is represented there. If you try to plot this, you get an X axis that skips around (depending on how Metabase detects the series type, in my case it selected Linear, but if it selects Ordinal then months will be missing).

The solution is to generate the list of months first, then join the counts to it. In H2 and PostgreSQL, you can use the generate_series() function to do this. If your database doesn't have this feature then you may have to manually create the table or use a TVF to generate it.

select m.monthnum from generate_series(1,12,1) m(monthnum) order by 1;

 monthnum 
----------
        1
        2
        3
        4
        5
        6
        7
        8
        9
       10
       11
       12

Now, if we left join the second query to the first, all the months will be represented even if there were no transactions counted for that month (if you want, you can use COALESCE(t.count, 0) to convert the nulls to zero):

select m.monthnum, t.count from generate_series(1,12,1) m(monthnum)
left join (
select extract(month from created_at) as monthnum, count(*) as count from orders
where random() > 0.9995
group by monthnum
) t using (monthnum)
order by monthnum

 monthnum | count 
----------+-------
        1 |     1
        2 |     1
        3 |     1
        4 |      
        5 |      
        6 |      
        7 |     1
        8 |      
        9 |     1
       10 |      
       11 |      
       12 |     2

[twinsa]

Thank you.
Got it. I'll try.