KNOWLEDGE BASE

Determine if Multiple Dimensions Contain the Same Text Value


Published: 21 Jun 2017
Last Modified Date: 23 Jun 2017

Question

How to compare multiple string dimensions to determine if the member value of each dimension on the same row is the same ignoring NULL values.

If all of the dimensions have the same value, ignoring NULL values, then return that value, otherwise return a text value flagging that there are multiple values.

Environment

Tableau Desktop

Answer

CLICK TO EXPAND SOLUTION
OPTION 1: Pivot the data
  1. Pivot the data so that all of the dimension names are in one field and all of the dimension members are in another field.
  2. 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.
  3. Select Analysis > Create calculated field
  4. 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:
  1. Select Analysis > Create calculated field
  2. 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
    }
  3. Repeat step 1 & 2 for every group of two dimension
  4. Select Analysis > Create calculated field
  5. 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
    }
  6. Repeat step 4 & 5 for every set of two level 1 calculations
  7. Select Analysis > Create calculated field
  8. 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
    }
Did this article resolve the issue?