Calculate a score based on multiple columns (case) as a custom column

[ni-ka]

Hi,

I am trying to use the custom question screen to calculate a score based on multiple columns. However, as soon as I try to combine multiple case expressions in a custom column I get an error:

Custom column:
case([Com Strategy] = "Yes", 1, 0) + case([Com Analytics] = "Yes", 1, 0)

Here is the error I get:
Value does not match schema: {:query {:expressions {:score (named [nil (named (named (not (some-matching-condition? a-clojure.lang.PersistentVector)) "Must be a valid instance of one of these clauses: :field-id, :field-literal, :joined-field, :fk->, :datetime-field, :expression, :binning-strategy") "x") (named (named (not (some-matching-condition? a-clojure.lang.PersistentVector)) "Must be a valid instance of one of these clauses: :field-id, :field-literal, :joined-field, :fk->, :datetime-field, :expression, :binning-strategy") "y")] "Must be a valid instance of one of these clauses: :+, :-, :/, :*, :coalesce, :length, :floor, :ceil, :round, :abs, :power, :sqrt, :exp, :log")}}}

Is my syntax incorrect?

Otherwise I can use custom SQL but as I am exploring data and would like to quickly change the fields returned (score stays the same) and explore the clusters I might get. Here is the valid SQL I use:

SELECT  "source"."Score" AS "Score", Count(*)
FROM (
    SELECT 
        "public"."survey"."org_name" AS "org_name", 
        CASE WHEN "public"."survey"."com_analytics" ilike '%yes%' THEN 3 ELSE 0 END +
        CASE WHEN "public"."survey"."com_online_community" ilike '%yes%' THEN 3 ELSE 0 END +
        CASE WHEN "public"."survey"."com_strategy" ilike '%yes%' THEN 5 ELSE 0 END 
                
        AS "Score" 
        FROM "public"."survey") "source"
GROUP BY "source"."Score"
ORDER BY "source"."Score" ASC

Thanks for any pointers!

[flamber]

Hi @ni-ka
I have created an issue for it - we are working on a lot of fixes for Custom Expressions in upcoming 0.39
https://github.com/metabase/metabase/issues/15107 - upvote by clicking on the first post

[ni-ka]

Thanks @flamber for creating the issue. I’ve upvoted it.

When I use custom SQL, is there a way to enable exploration feature (at least looking at the records affected), for example by returning an ID or similar?

[flamber]

@ni-ka Currently drill-through is only available on questions created in the UI, since Metabase doesn’t have enough metadata to understand the results of the SQL.