Append a Label to all Values

[PaulM1]

I would like to try and replicate some of the behavior of the duration dimension_group. Such as, the resulting values have a label appended to them. For example, if my Explore has a Duration dimension_group:

dimension_group: TravelTime1 {
  label: "Travel Time 1"
  type: duration
  sql_start: ${TABLE}."START_TIME" ;;
  sql_end: ${TABLE}."END_TIME" ;;
  intervals: [ hour, minute ]
}

The resulting query appends the name of the interval to the values. If I select ‘Hours’ the resulting query includes “hours” in the results:

The nice thing about this is that the underlying data is still numeric. i.e. - I can apply numeric filtering, and can use numeric functions in table calculations.

Unfortunately, dimension_groups are not available for every need. They also have some other limitations. So I would like to replicate this with a standard Dimension that has some dynamic functionality:

parameter: p_UnitOfMeasure_Distance {
  label: "Unit of Measure - Distance"
  type: string
  description: "Use with 'Distance' Dimension (default is Miles)"
  default_value: "Miles"
  allowed_value: {
   label: "Miles"
   value: "Miles"
  }
  allowed_value: {
   label: "Kilometers"
   value: "Kilometers"
  }
}



dimension: Distance {
  label: "Distance"
  description: "Typical driving route (use with 'Unit of Measure' Filter-Only Field)"
  value_format_name: nvf_Quantity_def
  type: number
  sql: CASE {% parameter p_UnitOfMeasure_Distance %}
    WHEN 'Miles' THEN ${TABLE}."DISTANCE" / 1609.344
    WHEN 'Kilometers' THEN ${TABLE}."DISTANCE" / 1000
    END ;;
}

The data type needs to remain as a number. I would only like the data labeled somehow so users are clear on what unit of measure is being used.

I have considered creating my own Dimension Groupings (see below). But I would much prefer a solution to the example above.

dimension: Distance_miles {
  group_label: "Distance"
  group_item_label: "Miles"
  label: "Distance - Miles"
  description: "Typical driving route"
  type: number
  value_format_name: nvf_Quantity_def
  sql: ${TABLE}."DISTANCE" / 1609.344 ;;
}

dimension: Distance_kilometers {
  group_label: "Distance"
  group_item_label: "Kilometers"
  label: "Distance - Kilometers"
  description: "Typical driving route"
  type: number
  value_format_name: nvf_Quantity_def
  sql: ${TABLE}."DISTANCE" / 1000 ;;
}

[PaulM1 · Accepted solution]

Solved with html.

Hint: Use {{rendered_value}} to retain the value_format. Use {{linked_value}} to persist the value_format, and also retain the clickable options in results.

parameter: p_UnitOfMeasure_Distance {
  label: "Unit of Measure - Distance"
  type: unquoted
  description: "Use with 'Distance' Dimension (default is Miles)"
  default_value: "Miles"
  allowed_value: {
    label: "Miles"
    value: "Miles"
  }
  allowed_value: {
    label: "Kilometers"
    value: "Kilometers"
  }
}


dimension: Distance {
  label: "Distance"
  type: number
  description: "Typical driving route (use with 'Unit of Measure' Filter-Only Field)"
  value_format_name: nvf_Quantity_def
  sql: CASE '{% parameter p_UnitOfMeasure_Distance %}'
    WHEN 'Miles' THEN ${TABLE}."DISTANCE_METERS" / 1609.344
    WHEN 'Kilometers' THEN ${TABLE}."DISTANCE_METERS" / 1000
    ELSE ${TABLE}."DISTANCE_METERS"
    END ;;
  html: {{rendered_value}}
    {% if p_UnitOfMeasure_Distance._parameter_value == 'Miles' %}
      Miles
    {% elsif p_UnitOfMeasure_Distance._parameter_value == 'Kilometers' %}
      Kilometers
    {% else %}
      Meters
    {% endif %};;
}