Dynamic named_value_Format

Looker Q&A Looker
1

is there anyway to make this code work from this thread:

https://community.looker.com/lookml-5/dynamic-value-format-name-19992?postid=52211#post52211

I’m trying to dynamically change the measure value_format based on the value of a parameter. but I keep getting error trying to use the suggestion in attached thread.

this is my code:


  parameter: Metric_Selection {
    type: string
    description: "Parameter for setting up breakdown"
    allowed_value: { value: "Clicks" }
    allowed_value: { value: "Impressions" }
    allowed_value: { value: "Cost" }
    allowed_value: { value: "CTR" }
    allowed_value: { value: "CPC" }
    allowed_value: { value: "CVR" }
    allowed_value: { value: "CPA" }
    allowed_value: { value: "Revenue" }
    allowed_value: { value: "ROAS" }
    allowed_value: { value: "Conversion" }

  }

dimension: format_dynamic {
  sql: if({% parameter Metric_Selection %} = "Clicks" ,"#,##0.00",
       if({% parameter Metric_Selection %} = "Impressions" ,"#,##0.00",
       if({% parameter Metric_Selection %} = "Cost","$#,##0.00",
       if({% parameter Metric_Selection %} = "CTR","0.00\\%",
       if({% parameter Metric_Selection %} = "CPC","$#,##0.00",
       if({% parameter Metric_Selection %} = "CVR","0.00000\\%",
       if({% parameter Metric_Selection %} = "CPA","$#,##0.00",
       if({% parameter Metric_Selection %} = "ROAS","0.00\\%",
       if({% parameter Metric_Selection %} = "Conversion","#,##0.00","#,##0.00")))))))));;

}

  measure: final_metrics {
    label_from_parameter: Metric_Selection
    group_label: "Transaction_Info"
    label: "Transaction_Info"
    description: "Select your desired metric"
    sql:
      CASE
       WHEN {% parameter Metric_Selection %} = "Clicks" THEN sum(${TABLE}.Clicks)
       WHEN {% parameter Metric_Selection %} = "Impressions" THEN sum(${TABLE}.Impressions)
       WHEN {% parameter Metric_Selection %} = "Cost" THEN sum(${TABLE}.Cost) 
       WHEN {% parameter Metric_Selection %} = "Revenue" THEN sum(${TABLE}.total_rev)
       WHEN {% parameter Metric_Selection %} = "CTR" THEN case when sum(impressions)=0 then 0 else sum(clicks)/sum(impressions) end 
       WHEN {% parameter Metric_Selection %} = "CPC" THEN case when sum(clicks)=0 then 0 else sum(cost)/sum(clicks) end 
       WHEN {% parameter Metric_Selection %} = "CVR" THEN case when sum(clicks)=0 then 0 else sum(${TABLE}.overall_conversions)/sum(clicks) end
       WHEN {% parameter Metric_Selection %} = "CPA" THEN case when sum(${TABLE}.overall_conversions)=0 then 0 else sum(cost)/sum(${TABLE}.overall_conversions) end
       WHEN {% parameter Metric_Selection %} = "ROAS" THEN  case when sum(${TABLE}.cost)=0 then 0 else sum(${TABLE}.total_rev)/sum(cost)*100 end
       WHEN {% parameter Metric_Selection %} = "Conversion" THEN sum(${TABLE}.overall_conversions)
       ELSE NULL
      END ;;
  }

is there anyway to enforce the format_dynamic to my measure final_metrics?