Dynamic value format name

[Dawid]

I sent it to Looker support as well but I was wondering if anybody has similar requirement.

I have 30+ measures that come from an aggregation table in an original currency. Instead of creating two extra fields per measure with suffixes _eur and _usd I decided to do the following.

Create parameter to allow people to choose whether they want to see converted values:

parameter: conversion_currency {
    type: unquoted
    default_value: "1"
    allowed_value: {
      label: "Euro"
      value: "EUR"
    }
    allowed_value: {
      label: "US Dollars"
      value: "USD"
    }
  }

And then based on this parameter I select a field from the table. Each row has both FX rates already available.

dimension:fx_rate {
    sql:  {% if conversion_currency._parameter_value == "EUR" %}
            ${TABLE}.fx_rate_eur
          {% elsif conversion_currency._parameter_value == "USD" %}
            ${TABLE}.fx_rate_usd
          {% else %}
            1
          {% endif %}  ;;
  }

Here is a sample measure:

measure: net_value {
    type: sum
    sql: ${TABLE}.net_value / ${fx_rate} ;;
    value_format_name: decimal_2
  }

When no currency conversion is selected it’s defaulting to net value / 1, which is great. The problem I’m having is to apply specific formatting depending on the parameter.

As far as I know I can’t use parameters in definition of my own format but it’s basically what would solve the problem

named_value_format: currency_dynamic {
  value_format: {% if conversion_currency._parameter_value == "EUR" %}
                  "eur_2"
                {% elsif conversion_currency._parameter_value == "USD" %}
                  "usd_2"
                {% else %}
                  "decimal_2"
                {% endif %}
}

and then I could use

measure: net_value {
  value_format_name: currency_dynamic
}

Which means if I had to add new currency conversion, I need to only change it in 3 places and all measures would work automatically. Any ideas how to achieve right now?

The only thing I saw as possible solution was:

html:  
          {% if conversion_currency._parameter_value == "EUR" %}
            €{{ rendered_value }}
          {% elsif conversion_currency._parameter_value == "USD" %}
            ${{ rendered_value }}
          {% else %}
            {{ rendered_value }}
          {% endif %};;

But I can’t remember where but I think I saw somebody having pointed out some shortcomings with this approach. Also this is a lot of code to repeat , not sure if I can put this into another parameter or a constant but then again constant can’t be defined in a view file