KNOWLEDGE BASE

Query Generated for Calculated Measure Not Modified by Parameter Selected


Published: 04 Nov 2020
Last Modified Date: 05 Nov 2020

Issue

When using a calculation with a CASE statement that selects a measure depending on a parameter, the query that Tableau Desktop generates is not modified by the parameter value that is selected. Instead, all of the measures in the CASE statement appear in the query. This results in slower performance.

An example of the calculation, where [Measure Selector] is a parameter:

CASE [Measure Selector]
  WHEN 'Sales' THEN SUM([Sales])
  WHEN 'Profit' THEN SUM([Profit])
  WHEN 'Inventory' THEN ROUND(AVG([Inventory])*100,2)
  WHEN 'Margin' THEN ROUND(AVG([Margin])*100,2)
END



 

Environment

  • Tableau Desktop

Resolution

As a possible workaround, use non-aggregated measures in the calculation instead.

For example:
CASE [Measure Selector] 
  WHEN 'Sales' THEN [Sales] 
  WHEN 'Profit' THEN [Profit] 
  WHEN 'Inventory' THEN [Inventory]  
  WHEN 'Margin' THEN [Margin] 
END


The required aggregations can be applied using a second calculation, such as:

IF [Measure Selector] IN ('Sales', 'Profit') 
THEN SUM([Selected Non-Agg Measure]) 
ELSEIF [Measure Selector] IN ('Inventory', 'Margin') 
THEN ROUND(AVG([Selected Non-Agg Measure])*100, 2) 
END

Cause

This behavior is currently under investigation.
Did this article resolve the issue?