Switch between fields based on selection

[BrianLewis]

I have a KPI that shows a sum(measure). I have a field that just says 'N/A'. If someone selects a certain field from a Parameter Selections, I need the KPI measure to change from a measure to that dimension so the KPI will say 'N/A'. 

How can I get the parameter selection to completely switch the field that is selected in the KPI? 

[salehaxsid · Accepted solution]

You can use parameters this way to implement KPIs on Selection only:

1. Create a parameter to select different measures at a time:

parameter: measure_selection{ 
type: unquoted
allowed_value: {
label: "Sales"
value: "Total_Sales"  --[name of sum(measure)]
}

allowed_value: {
label: "Total Orders"
value: "Total_Orders" --[name of sum(measure)]
}
}
2. Create a measure that is changes its measure according to selection using liquid templated filter:
measure : measure_select {
sql: {% if measure_selection._parameter_value == 'Total_Sales' %}
${Total_Sales}
{% elsif measure_selection._parameter_value == 'Total_Orders' %}
${Total_Orders}
{% endif %} ;;
}

if this solves your problem then do mark it as a solution. Thanks!

[salehaxsid · Accepted solution]

You can use parameters this way to implement KPIs on Selection only:

1. Create a parameter to select different measures at a time:

parameter: measure_selection{ 
type: unquoted
allowed_value: {
label: "Sales"
value: "Total_Sales"  --[name of sum(measure)]
}

allowed_value: {
label: "Total Orders"
value: "Total_Orders" --[name of sum(measure)]
}
}
2. Create a measure that is changes its measure according to selection using liquid templated filter:
measure : measure_select {
sql: {% if measure_selection._parameter_value == 'Total_Sales' %}
${Total_Sales}
{% elsif measure_selection._parameter_value == 'Total_Orders' %}
${Total_Orders}
{% endif %} ;;
}

if this solves your problem then do mark it as a solution. Thanks!

[salehaxsid · Accepted solution]

also if you want to select dimension and measure together in a single parameter then you can create dimension of a measure using subquery and cte in sql.
for example:

1. Create a dimension parameter 

parameter: dimension_selection{ 
type: unquoted
allowed_value: {
label: "Sales"
value: "Total_Sales"  --[name of sum(measure) which is converted into dimension]
}

allowed_value: {
label: "Order Id"
value: "Order_Id" --[name of dimension Order_Id]
}
}

2. Create a Dimension that shows sum of sales using cte and subquery in sql parameter.

dimension: Total_Sales{
type: number
sql: (with cte as (select "total" as dimension, sum(${unit_price}*${quantity}*((1-${discount})/100)) as total_sales
from `order-details` as orderdetails group by dimension) select total_sales from cte) ;;
value_format_name: "usd"
}

the sql statement here creates a hardcode dimension of a single string value and calculates sum of sales row wise and then group it according to hardcode dimension, it will give total sales values in KPI and you can use it in custom dimension that gets filtered by parameters.

3. Create a dimension field that is changes its values according to selection using liquid templated filter:
dimension : dimension_select {
sql: {% if measure_selection._parameter_value == 'Total_Sales' %}
${Total_Sales}
{% elsif measure_selection._parameter_value == 'Total_Orders' %}
${Total_Orders}
{% endif %} ;;