I have two comumns in a table: [Max Seats] and [Seats Used]. I'd like to filter the table to show only records where [Seats Used] > [Max Seats], and I'm not seeing how to do this with a custom expression. I've tried multiple attempts using the case function, and nothing has worked. I would welcome some help on what seems like a very basic question.
Hi @johnburk
Please post "Diagnostic Info" from Admin > Troubleshooting.
I'm not sure what the problem is. What is happening, when you try? My first guess is that the columns might not be numeric, but actually strings.
It should be as simple as this - Discount=Used and Tax=Max in my little example:
That's what I'm trying to do, but the filter isn't returning anything:
Here's the diagnostic info:
{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/93.0.4577.82 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.11+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.11",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.11+9",
"os.name": "Linux",
"os.version": "4.14.219-164.354.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"mongo",
"bigquery",
"h2"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.9"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-08-03",
"tag": "v1.40.2",
"branch": "release-x.40.x",
"hash": "b884d29"
},
"settings": {
"report-timezone": null
}
}
}
Also, both Seats Used and Max Seats are numbers:
@johnburk I'm guessing this is BigQuery?
I would need to see more details about the fields.
Can you go to Admin > Data Model > (db) > (table) > (field) gear-icon for both "Max Seats" and for "Seats Used".
Then the URL should look like /admin/datamodel/database/1/table/4/123/general where 123 would be the field ID.
And then go to the URL /api/field/123 (for each field), and include the output here (you can redact connection details).
The table isn't too big—maybe a few thousand rows?
I checked data model for both Max Seats and Seats Used and their type is Quantity.
Both look like this:
@johnburk Please read what I wrote again. I need the metadata for the fields to be able to understand what is going on. I cannot see anything from a screenshot.
Sorry, Missed that second step:
Here's the Seats Used field:
{"description":null,"database_type":"java.lang.Integer","semantic_type":"type/Quantity","table_id":39,"coercion_strategy":null,"table":{"description":null,"entity_type":"entity/GenericTable","schema":null,"db":{"description":null,"features":["basic-aggregations","nested-fields","native-parameters","case-sensitivity-string-filter-options"],"cache_field_values_schedule":"0 0 13 * * ? *","timezone":null,"auto_run_queries":true,"metadata_sync_schedule":"0 17 * * * ? *","name":"Pivot Production","caveats":null,"is_full_sync":true,"updated_at":"2021-06-03T01:02:23.004541Z","details":{,"is_sample":false,"id":34,"is_on_demand":false,"options":null,"engine":"mongo","refingerprint":null,"created_at":"2021-06-01T15:14:08.259695Z","points_of_interest":null},"show_in_getting_started":false,"name":"licenses","caveats":null,"updated_at":"2021-06-01T15:14:57.755282Z","entity_name":null,"active":true,"id":39,"db_id":34,"visibility_type":null,"field_order":"database","display_name":"Licenses","created_at":"2021-06-01T15:14:08.564024Z","points_of_interest":null},"name":"seatsUsed","fingerprint_version":5,"has_field_values":"none","settings":null,"caveats":null,"fk_target_field_id":null,"dimensions":[],"updated_at":"2021-09-17T00:57:51.977391Z","custom_position":0,"effective_type":"type/Integer","active":true,"parent_id":null,"id":790,"last_analyzed":"2021-06-01T15:14:57.868708Z","position":13,"visibility_type":"normal","preview_display":true,"display_name":"Seats Used","database_position":16,"name_field":null,"fingerprint":{"global":{"distinct-count":506,"nil%":0.0},"type":{"type/Number":{"min":0.0,"q1":197.26926020127294,"q3":585.8731546069171,"max":8858.0,"sd":708.3205139351697,"avg":509.18346253229976}}},"created_at":"2021-06-01T15:14:24.210746Z","base_type":"type/Integer","points_of_interest":null}
and here is max Seats:
{"description":null,"database_type":"java.lang.Integer","semantic_type":"type/Quantity","table_id":39,"coercion_strategy":null,"table":{"description":null,"entity_type":"entity/GenericTable","schema":null,"db":{"description":null,"features":["basic-aggregations","nested-fields","native-parameters","case-sensitivity-string-filter-options"],"cache_field_values_schedule":"0 0 13 * * ? *","timezone":null,"auto_run_queries":true,"metadata_sync_schedule":"0 17 * * * ? *","name":"Pivot Production","caveats":null,"is_full_sync":true,"updated_at":"2021-06-03T01:02:23.004541Z","details":{,"is_sample":false,"id":34,"is_on_demand":false,"options":null,"engine":"mongo","refingerprint":null,"created_at":"2021-06-01T15:14:08.259695Z","points_of_interest":null},"show_in_getting_started":false,"name":"licenses","caveats":null,"updated_at":"2021-06-01T15:14:57.755282Z","entity_name":null,"active":true,"id":39,"db_id":34,"visibility_type":null,"field_order":"database","display_name":"Licenses","created_at":"2021-06-01T15:14:08.564024Z","points_of_interest":null},"name":"maxSeats","fingerprint_version":5,"has_field_values":"none","settings":null,"caveats":null,"fk_target_field_id":null,"dimensions":[],"updated_at":"2021-09-17T00:53:36.813275Z","custom_position":0,"effective_type":"type/Integer","active":true,"parent_id":null,"id":792,"last_analyzed":"2021-06-01T15:14:57.868708Z","position":2,"visibility_type":"normal","preview_display":true,"display_name":"Max Seats","database_position":3,"name_field":null,"fingerprint":{"global":{"distinct-count":186,"nil%":0.0103359173126615},"type":{"type/Number":{"min":1.0,"q1":334.34288786684436,"q3":929.6495905544251,"max":28300.0,"sd":2128.861701314155,"avg":1054.3146214099218}}},"created_at":"2021-06-01T15:14:24.217101Z","base_type":"type/Integer","points_of_interest":null}
@johnburk I have created an issue about this with more details and possible workaround:
https://github.com/metabase/metabase/issues/17963 - upvote by clicking 
on the first post