Dynamic measure type

[BrianLewis]

is it possible to have a dynamic type value as shown in green? I know this format doesn't work, but I was wondering if something like this is even possible. 

measure: rev {
group_label: "KPI Values"
label: "rev"
type: {% if xxx._parameter_value == 'Stay' %} string {% else %} sum
sql:
{% if xxx._parameter_value == 'Stay' %}
"N/A"
{% else %}
coalesce(${xxx.rev},0)
{% endif %};;

[sam8 · Accepted solution]

Hey Brian, I can confirm that there's no dynamic way to change the type of the field. That's an immutable part of its definition.

Of course, you can do whatever you want in the sql parameter, as you've done here. You could also reference two different fields, one which is a string and one which is a sum. But at the end of the day, the measure's type needs to be static.

In this example, I don't quite understand what you'd gain from making this measure a sum? Since in this example it'd be a sum of the string "N/A". If you want to share more ideas about your use case, I might be able to help brainstorm further!

[sam8 · Accepted solution]

I see. What about performing that check in the html parameter instead of the sql parameter? And then let the field remain a sum type with the same coalescing logic.

[sam8 · Accepted solution]

Hey Brian, I can confirm that there's no dynamic way to change the type of the field. That's an immutable part of its definition.

Of course, you can do whatever you want in the sql parameter, as you've done here. You could also reference two different fields, one which is a string and one which is a sum. But at the end of the day, the measure's type needs to be static.

In this example, I don't quite understand what you'd gain from making this measure a sum? Since in this example it'd be a sum of the string "N/A". If you want to share more ideas about your use case, I might be able to help brainstorm further!

[BrianLewis]

Thanks for the feedback. 

We have a revenue field that is dependent on a parameter value, but based on a combination, showing revenue doesn't make sense and we don't want it to show 0 as revenue during that situation (which is what we are getting now). We want it to be very clear and say "N/A" so the users understand revenue isn't relevant and not get confused when it says 0 as revenue. 

[sam8 · Accepted solution]

I see. What about performing that check in the html parameter instead of the sql parameter? And then let the field remain a sum type with the same coalescing logic.

[sam8 · Accepted solution]

Something like the example here: