As we have Time Grouping to dynamically change x-axis of any datetime based visualization, can we do the same for Location grouping, like Country—>City—->Street. ?
There’s no intrinsic support for this in Metabase, but you can hack it in by using a filter with a defined set of values, and a CASE statement that permutes the query based on the value of the variable. I’ll see if I can write up an example later.
EDIT: A simple example. The query below has a variable, {{column}}, that selects the column to group by. tenk1 is a database used in the PostgreSQL testing suite, but is useful as it contains columns with a set number of unique values. Grouping by the column should return a number of rows indicated by the name, with a count of number of rows that have unique values. For example, grouping by ‘four’ returns 4 rows with a value of 2500, e.g., 10,000 divided by 4.
select count(*) from tenk1
group by
case when {{column}}='two' then two
when {{column}} = 'four' then four
when {{column}} = 'ten' then ten
when {{column}} = 'hundred' then hundred
when {{column}} = 'thousand' then thousand
when {{column}} = 'twothousand' then twothousand
when {{column}} = 'fivethousand' then fivethous
when {{column}} = 'tenthousand' then tenthous
END
The variable is set up as a Text variable with a dropdown list filled with custom values.
CASE is required here as the variable is inserted as a text string via a bind variable and is not interpreted as an identifier. Without it you get a count of all rows as the GROUP BY expression is a constant. On the plus side, the Metabase variable can use friendly strings, and you can translate them in the query. (This may not be true for all database engines, but should be true for most popular RDBMS.)
You must use a SQL query for this question as the notebook editor can’t take an expression in GROUP BY. Again, this is a very simple example; the resultant query must still comply with the rules of GROUP BY regarding grouped and ungrouped columns.
1 Like