Unable to Convert Measure to Dimension

Issue

When you create a calculated field, Tableau Desktop might recognize the field as a measure rather than a dimension. When you try to convert the field to a dimension, it might not be possible.

Environment

Tableau Desktop

Resolution

Option 1: Add a FIXED statement

The level of detail (LOD) function FIXED can be converted into a dimension. Depending on the original calculation the exact solution will differ, but remember that the calculation as a whole must be non-aggregate (FIXED, non-aggregated fields, parameters, and constant literal values are all non-aggregate). See the following examples:

Example 1

Can only be a measure:

IF SUM([SALES]) < 100
THEN "Low"
ELSE "High"
END

Can be converted to dimension:

IF { FIXED [Region] : SUM( [Sales] ) } < 100
THEN "Low"
ELSE "High"
END

Example 2

Can only be a measure:

AVG( 
   IF { FIXED [Region] : SUM( [Sales] ) } < 100 
   THEN "Low" 
   ELSE "High" 
   END 
)

Can be converted to dimension:

{FIXED [Category] : AVG(
   IF { FIXED [Region] : SUM( [Sales] ) } < 100
   THEN "Low"
   ELSE "High"
   END
) }

Example 3

Can only be a measure:

ATTR( [Category] ) + " value"

Can be converted to dimension:

{ FIXED [Order ID] : MIN( [Category] ) } + " value"

Note: ATTR() had to be replaced with MIN() because ATTR() is a table function and cannot be used inside of a FIXED expression. ATTR() returns NULL if there is more than one unique value of [Category] in one partition (row, cell, bar, line dot, etc...) in the view, which helps identify data quality issues. MIN() on the other hand will always return the minimum value, which is the first alphabetically for string values. As long as MIN( [Category] ) is fixed to a dimension or combination of dimensions that always has only one category value, then there will be no issues.

Option 2: Remove Aggregations

Sometimes the aggregation is not actually needed. For example, { FIXED [Region] : SUM( [Sales] } returns the total sales per region, but if the data source only has one record per region then [Sales] will return the same value.

Example 1

Can only be a measure:

IF SUM([SALES]) < 100
THEN "Low"
ELSE "High"
END

Can be converted to dimension:

IF [Sales] < 100
THEN "Low"
ELSE "High"
END

Option 3: Use a Tableau generated bin field

In some cases it may be possible to replace the original calculation with a Tableau generated bin field. See Create Bins from a Continuous Measure
for directions.

Option 4: Do the calculation in Tableau Prep

The output from Tableau Prep will always be non-aggregate and thus can always be converted to a dimension. Depending on the original calculation the steps needed in Tableau Prep will differ. In general, to replicate an expression like SUM( [Sales] ) in Tableau Prep, you will need an aggregate step.

Table calculations cannot be made non-aggregate as they cannot be placed inside of level of detail functions. Some table calculations can be recreated in Tableau Prep. For example, LOOKUP() can sometimes be replaced with a shifted-self join. See Lookup Table Calculation in Tableau Prep

Option 5: Join data rather than use data blending

Fields from a secondary data source must be aggregated. This is a limitation of data blending and there is no workaround. Consider a cross-database join or joining in Tableau Prep.

Cause

If an aggregation is used in the calculated field, the measure cannot be converted to a dimension, because the results of the calculation are dynamic.

For example, SUM( [Sales] ) will return different results when [Region] is on the Rows shelf versus when [Category] is on the Rows shelf. However the expression {FIXED [Region] : SUM( [Sales] ) } will always be the total sales per region. Adding the FIXED statement tells Tableau how to compute SUM(), which then tells Tableau how to group each record based on the total sales of the region it belongs to.

Additional Information

Why does Tableau use dimensions and measures?

Tableau takes advantage of common properties of data shared between very different data sources in order to make smart decisions about how to display data.

For example, your data source contains a field with numeric values but is named "ID." When you add "ID" to the Rows shelf instead of getting a meaningless bar chart that adds together all of the ID values, you get a row for each ID. How does Tableau know to do this? Fields named "ID" are a special case that are considered dimensions by default.

Dimensions contain data that is used to group other data, and thus Tableau knows that dimensions should generally be headers rather than charts, even if the dimension contains numeric data.

Dimensions and measures are concepts from data management that give Tableau clues about how the data should be displayed. For more information how Tableau handles dimensions and measures, see Dimensions and Measures, Blue and Green