Can't filter dashboard with double aggregation

Hello.

Why can’t we filter a dashboard when we are utilizing double aggregation?
Apparently when we use this type of aggregation, the filter is directed after the first aggregation, limiting the filter fields.

But if the filter was directed before the aggregations, we could apply that to every field.

{
“browser-info”: {
“language”: “pt-BR”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.7+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.7”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.7+10”,
“os.name”: “Linux”,
“os.version”: “4.14.200-155.322.amzn2.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“mysql”
],
“hosting-env”: “elastic-beanstalk”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “12.4”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-11-16”,
“tag”: “v0.37.2”,
“branch”: “release-x.37.x”,
“hash”: “25e5f70”
},
“settings”: {
“report-timezone”: null
}
}
}

Hi @beey
I don’t understand this and cannot reproduce. Can you reproduce with Sample Dataset?

Sure.
Create a question on Sample Dataset -> Select PRODUCTS -> Summarize MAX(PRICE) by CREATED_AT -> Summarize Again by MEAN(PRICE) by CREATED_AT.

Save the question and put into a dashboard.

Create a Category Filter on the dashboard and you will see that are not possible to filter any field on that question.

If we go back to the question -> exclude the second summarization -> save, go back to the dashboard and create the filter, it’s possible again to filter the fields.

@beey But you are only grouping by “Created At” as a possible field to filter by - and with your example, I can filter with a Time type.
If I add Category too, then I can filter by that too.


@flamber Ok, now I understood. I have to first aggregate with all the fields that I want to filter, and then aggregate again to show that in the graphic. I though that I could filter everything before the first grouping was made.

@beey It’s only the last “layer” that is being sent in the metadata. This is also only of the blockers from being able to do other interesting things.
We have an issue open about getting more detailed information, so we are able to do more advanced parsing:
https://github.com/metabase/metabase/issues/5594 - upvote by clicking :+1: on the first post
There are other issues too, but can’t remember the exact ones.

1 Like

@flamber thank you for super clear explanation.

I have question on this topic. I understood only the last "layer" is being sent in the metadata. But there are cases where you want to filter on the previous layer (first aggregation), and you do not want to group by that value on the second layer.

Example : I want the average revenue by month.

First layer : Metric : Revenue Group by : Month
Second Layer : Metric : Average(Revenue)

Now i want the average revenue by month for a customer. I should then filter the results of the first layer, but i cannot access it anymore right from a dashboard ?

@tpatulacci There's a lot of security involved here too. Example, let's say it's Paycheck instead of Revenue, and someone who might have permissions to view the question, but edit it, they should not be able to create a filter, where they can get information at person-level.

It's complicated and complex. We're trying to not over-complicate the GUI, but it has some disadvantages too.

If you need something like this, then you'll likely need to use SQL and filters:
https://www.metabase.com/learn/sql-questions/sql-cte
https://www.metabase.com/learn/sql-questions/field-filters

1 Like