Cannot filter on custome column

[Augustin1]

Hi,

Using metabase version v0.45.2, I created questions with custom column in order to translate data and make them readable for the client.
Then on a dashboard, I want to create a drop down filter on that custom column. But after several tries, I did not succeed. Any idea ? I can create filter begin with or stuff like that but for the client it is not really user friendly. It would be better to have a drop down list.

So how can I have a drop down in a filter using a custom column ?

Many thanks for the help.
Regards.

[TonyC]

I think you are hitting this issue:

Dashboard filter dropdown/search not shown for nested questions (Saved Questions and aggregated)

A workaround for this would be to create a Database view and make the custom column as a column in the view so metabase would see this view as the new table having data already translated

[Augustin1]

Hi,

Thanks for the quick reply.
I tried to create a model with translated column, to save my model in cache, like that I have a specific table with my translated column. Then I wanted to test when I add a filter on this column, if I have my drop down list but unfortunately as soon as I change the configuration to allow the cache of my model and when the cache is created I always have an error on my model telling me that my custom column does not exist, where as it exists inside the table created by metabase for the cache.
And as soon as I desactivate the cache of my model, when I refresh my model the error disappear.

Any idea for the issue with the cache of the model ? I can give you some logs if needed.

Regards,

[Augustin1]

Hi,

Anyone looked to my issue ?
How can I avoid having errors when activating cache on models with custom column ?

[Augustin1]

Hi,

Yesterday, I created a new model, not based on a question but based on SQL.
I activated the cache of models in administration panel.
I can use the model to create questions that are embedded into a dashboard.

When I create a filter linked to a column of my model, I cannot have a drop down list of my filter. Any idea why ? The colmun I link is of kind 'Category'.

Thanks for the help.

[TonyC]

Hi Augustin,

You need to update the Metadata of the model and map the column

You need to do an additional step in this case and map that column to the respective field in the Data Model Metadata. Then the dropdown is shown based on whatever is defined in Data Model for the mapped field.

[Augustin1]

Hi Tony,

Thanks for the reply. Unfortunately, I have map my column to the respective field in the data model Metadata.
See below the SQL for the model creation:

WITH ListePersonnel AS
(
    select distinct on (surgery_id, role_group_type) surgery_person.last_name, surgery_person.first_name, surgery_person.surgery_id, surgery_person.role_group_type
    from surgery_person
    inner join surgery on surgery.id = surgery_person.surgery_id
    where role_group_type in ('OPERATOR', 'COORDINATOR')
    order by surgery_id, role_group_type, is_main desc, last_name, first_name
),
ListeDiscProc AS
(
    select distinct on (surgery_id) surgery_act.surgery_id, surgery_act.surgical_specialty_name, surgery_act.sub_surgical_specialty_name , surgery_act.surgical_procedure_name
    from surgery_act
    inner join surgery on surgery.id = surgery_act.surgery_id
    order by surgery_id
)
select provider.name, provider.eht_code, replace(replace(provider.active::text, 'true', 'Oui'), 'false', 'Non') as "Actif ?",
       surgery.id, surgery.identifier, surgery.service_name, surgery.pre_operating_room_name, surgery.operating_room_name, surgery.schedule_identifier, 
       replace(replace(replace(surgery.nature, 'SCHEDULED', 'Hospitalisation'), 'AMBULATORY', 'Ambulatoire'), 'URGENCY', 'Urgence') as "Nature", surgery.surgery_date, surgery.operating_room_occupation_time_in_seconds, surgery.surgery_duration_in_seconds,
       surgery.md_total_price_incl_tax, surgery.imd_total_price_incl_tax, surgery.total_price_incl_tax, surgery.equipment_total_price_incl_tax, surgery.patient_surgical_risk_name, surgery.comment, 
       replace(replace(replace(replace(surgery.status, 'PENDING_VALIDATION', 'A Valider'), 'VALIDATED', 'Validé(e)'), 'INITIALIZED', 'Initialisé(e)'), 'MERGED', 'Fusionné(e)') as "Statut", 
       replace(replace(surgery.interrupted::text, 'true', 'Oui'), 'false', 'Non') as "Interrompue ?", surgery.interruption_date, surgery.interruption_reason_description, surgery.interruption_comment,
       initcap(concat(praticien.last_name, ' ', praticien.first_name)) as "Praticien", initcap(concat(coordinateur.last_name, ' ', coordinateur.first_name)) as "Coordinateur",
       ListeDiscProc.surgical_specialty_name, ListeDiscProc.sub_surgical_specialty_name, ListeDiscProc.surgical_procedure_name
from surgery 
inner join provider on provider.id = surgery.provider_id
left join ListePersonnel praticien on praticien.surgery_id = surgery.id 
                         and praticien.role_group_type = 'OPERATOR'
left join ListePersonnel coordinateur on coordinateur.surgery_id = surgery.id 
                         and coordinateur.role_group_type = 'COORDINATOR'
inner join ListeDiscProc on ListeDiscProc .surgery_id = surgery.id
order by provider.name, surgery.surgery_date desc

Here a capture of one column that is renamed and mapped :

And finally in my dashboard, I created a filter which is a drop down list and linked to the column:

But the filter does not show me any list:

Regards,

[TonyC]

If you go to Admin -> Data Model and check that specific column:

Whats the filtering on this field setup to?

Also how many unique names does that column contains?

[Augustin1]

Hi,
Here is the parameter of this column:

The thing is that I have other dashboard using SQL request, or questions, not using the model but directly the original tables. And on these dashboard the filtrer shows me a drop down list:

So to sum up, when using the column from a model, I donot have drop down list but when using the original table, I do have a drop down list.