Determine if Multiple Dimensions Contain the Same Text Value

Answer

CLICK TO EXPAND SOLUTION

OPTION 1: Pivot the data

Pivot the data so that all of the dimension names are in one field and all of the dimension members are in another field.
For some data sources, the fields can be pivoted in the Tableau Desktop connection. For more information about how to pivot data in Tableau Desktop, please see Pivot Data from Columns to Rows.
Select Analysis > Create calculated field

Name the calculated field "Comparison", enter the following formula and click OK:

{ FIXED [ID] :
IF COUNTD([Pivot Field Values]) <= 1
THEN MIN([Pivot Field Values])
ELSE "Mulitple Values"
END
}

CLICK TO EXPAND SOLUTION

OPTION 2: Use Calculations

If the structure of the data cannot be changed, we can return the same result with a series of calculations that compare each set of two calculations in a tournament style. For example, if there are 4 dimensions named [A], [B], [C], and [D] then we would create two "level one" calculations: one to compare [A] and [B], and one to compare [C] and [D]. Then we would create one "level two" calculation to compare the calculated fields [AB] and [CD].
The attached example workbook uses sample data to demonstrate the following directions:

Select Analysis > Create calculated field

Name the calculated field "comparison lvl 1 - AB", enter the following formula and click OK:

IF ISNULL( [Dimension A] )
OR ISNULL( [Dimension B] )
THEN IFNULL( [Dimension A],  "" ) +  IFNULL( [Dimension B], "" )
ELSEIF [Dimension A] = [Dimension B]
THEN [Dimension A]
ELSE "Multiple Values"
END
}

Repeat step 1 & 2 for every group of two dimension
Select Analysis > Create calculated field

Name the calculated field "comparison lvl 2 - ABCD", enter the following formula and click OK:

IF [comparison lvl 1 - AB] = ""
OR [comparison lvl 1 - CD] = ""
THEN [comparison lvl 1 - AB] + [comparison lvl 1 - CD]
ELSEIF [comparison lvl 1 - AB] = [comparison lvl 1 - CD]
THEN [comparison lvl 1 - AB]
ELSE "Multiple Values"
END
}

Repeat step 4 & 5 for every set of two level 1 calculations
Select Analysis > Create calculated field

Name the calculated field "comparison lvl 3", enter the following formula and click OK:

IF [comparison lvl 2 - ABCD] = ""
OR [comparison lvl 2 - EFGH] = ""
THEN [comparison lvl 2 - ABCD] + [comparison lvl 2 - EFGH]
ELSEIF [comparison lvl 2 - ABCD] = [comparison lvl 2 - EFGH]
THEN [comparison lvl 2 - EFGH]
ELSE "Multiple Values"
END
}

All Support