What ATTR() Does and Testing Whether ATTR() Returns * (Multiple Values)

Version 6

    About ATTR():

    ATTR() is a special Tableau function that effectively returns a record-level result as an aggregation. If there are multiple values of the record-level field in the current context then ATTR() will return * instead of a single value. For example in the Superstore demo data if we do ATTR(State) in a view with Region as a dimension there are multiple states for each Region so ATTR(State) will return * for each:
    Screen Shot 2018-10-06 at 12.17.52 PM.png

    How does ATTR() work?

    ATTR() is a Tableau generated aggregation that uses MIN() and MAX() in the data source. Tableau’s internal formula for ATTR() is effectively something like:
    IF ISNULL(MIN([field])) THEN
        Null // there are only Null values for the field in the current context
    ELSEIF MIN([field]) = MAX([field]) THEN
        MIN([field]) // there is only one value for the field in the current context
    ELSE
        * //a special Tableau value that indicates there are multiple values of the field
    END
    We won’t see ATTR() called in the queries that Tableau issues to the data source (for example when using the Performance Recorder), instead queries will include MIN([field]) and MAX([field]).

    What is ATTR() good for?

    There are three main uses of ATTR() in calculations and visualizations:
    1) When adding dimensions to the Tooltip Tableau automatically wraps them in the ATTR() aggregation so the added field does not change the viz Level of Detail. Otherwise adding a dimension to the tooltip could cause many views to "break" when the dimension being added is at a finer grain than the existing LOD. For example in this view State is added to Detail and now there is a text mark for each State:
    Screen Shot 2018-10-06 at 12.22.08 PM.png
    By forcing the dimensions on Tooltip to be aggregated with ATTR() Tableau prevents this kind of view from coming up, at the expense of having * show up in tooltips. If you want to see all of the values in the tooltips there are three common options:

     

    a) In Tableau v10.5 and higher use a separate worksheet with the detail and Tableau's viz in tooltip feature: Create Views for Tooltips (Viz in Tooltip) .
    b) Use a separate worksheet on a dashboard with the detail and a Filter Action to display the detail.
    c) Use a table calculation that concatenates string values - see the Making a String List calculation in Top 10 Table Calculations – The Next N, Where N >= 15 | Drawing with Numbers. This is the most complicated to set up as you will likely need additional Level of Detail expressions or table calculations for the measure(s) in the view.

     

    2) A second use of ATTR() is to supply the aggregations required for various calculated fields. For example in doing a SUMIF() equivalent we might write it as IF [Region] = 'West' THEN [Sales] END and wrap that in a SUM(), or use IF ATTR([Region]) = 'West' THEN SUM([Sales]) END to have the entire calculation at the aggregate level. In the latter case using ATTR([Region]) makes the calculation only return a value if there is only one value of of Region in the current context and that Region is West. Another example is for table calculations, for example RANK_DENSE(ATTR([State]), ‘asc’) will numerically rank states in alphabetical order. A final example is when using dimensions from other data sources from Tableau data blends in calculated fields, since Tableau requires that the values from other data sources to be aggregated it automatically wraps those dimensions in ATTR().

     

    Note that at least as of Tableau v2018.2 ATTR() is not supported as an aggregation inside Level of Detail expressions. In that case you'll need to use MIN() or MAX() as the aggregation and if you need the testing that ATTR() does then write your own formula similar to the one above.

     

    3) Configuring the compute using of table calculations when there are many dimensions in the view is more complicated due to the extra clicks required to turn on or off the compute using for all the dimensions. In many cases the level of detail of the viz is effectively set by only one or a few dimensions and the rest aren't necessary. For example in this view to rank each Product Name in for each Year after aggregating both Category & Subcategory with ATTR() the compute using only needs to be set to Product Name instead of Product Name, Category, and Sub-Category:
    Screen Shot 2018-10-06 at 12.40.30 PM.png

    Why use ATTR() instead of MIN() or MAX()?

    MIN() will return the first value based on the alphanumeric sort, and MAX() will return the last value. So when using MIN() or MAX() there's no indication that there might be multiple value(s) in the context and there are situations where we might want to know that, especially if we're not sure about the true grain of the data and whether that might change over time.

     

    Here's an example based on real-world use in a healthcare environment: There's a data source that we think has one record per patient encounter, so we could use the distinct Encounter ID and  ATTR([Primary Diagnosis]) to get the primary (main) diagnosis of each patient. However due to later changes in how the patient encounter is evaluated the primary diagnosis might change and a new record be created for that encounter (ideally with some sort of version field), so now there would be two records for the Encounter ID with two different primary diagnoses. In that case the ATTR([Primary Diagnosis]) would return * and any calculations based on that field would return Null, which in this case would be preferable to returning the possibly incorrect primary diagnosis if we were using MIN() or MAX() as the aggregation.

    A Note on ATTR()'s Performance

    Since ATTR() requires two aggregations - MIN() and MAX() in the query - and additional computation in Tableau ATTR() will be slower than directly using MIN() or MAX(). If you have a number of dimensions using ATTR() in a view and need to improve performance then consider switching the aggregations to MIN() or MAX(). Of course you'll need to be wary of cases where there might be multiple values that would be ignored by MIN() or MAX() as noted in the prior paragraph.

    Tableau Data Blends and ATTR()

     

    As noted above in the section on using ATTR() in calculated fields Tableau automatically uses ATTR() to aggregate dimensions from other data sources used in calculations. In addition when we use secondary source dimensions on Rows, Columns, Pages, or the Marks Card even though the pills don't show any aggregation keywords the secondary dimensions are effectively aggregated with ATTR(). This is why we'll see * at times for secondary dimensions. There are three general workarounds for this:

     

    a) Where possible use primary dimensions instead of secondary dimensions.

    b) Add additional dimensions as linking dimensions.

    c) Use a join instead of a data blend.

    Testing Whether ATTR() Returns Multiple Values

    There are times when we want to know whether ATTR() is returning only one value or multiple values. There's an additional complication in that ATTR() ignores Null values, as we can see in this list of examples:
    DescriptionExampleCardinality of Result SetResult of ATTR(ID)Result of ISNULL(ATTR(ID)
    Multiple Values1,2,32+*False
    Multiple w/Null1,2,Null2+*False
    Single Value1,1,11the value (1 for this example)False
    All NullNull, Null, Null1NullTrue
    Single Value and Null1,1,Null2the value (1 for this example)False
    Therefore, it takes a few tests to identify whether the cardinality is 2+ (multiple values) or 1 (a single value).
    Multiple Value Test for ATTR() Formula:

    // If all records are Null then there is only one value - Null

    IF COUNT([ID])==0 THEN

        "All Null"

    // Tests whether there are multiple values, we'll ignore if there are multiple values & Null

    ELSEIF MIN([ID]) != MAX([ID]) THEN

        "Multiple"

    //This covers the case when the set of values being tested contains 1 distinct value and Null.

    //ATTR([value]) would return a single value and ignore the Null, and therefore be skipped in

    //the above tests and indicate a single result. If that is desired, then either delete or comment

    //out these two lines:

    ELSEIF COUNT([ID]) != SUM([Number of Records]) THEN

        "Single Value plus Null"

    //Otherwise there is a single value

    ELSE

        "Single"

    END

     

    If you would just like a Boolean True/False result for ATTR() then here's another calculation for that:

     

    // returns True if there are multiple values and False for everything else

    // including Null values. Ignores Null values when there is 1 distinct value + Null(s)

    MIN([ID]) != MAX([ID])

    AND NOT ISNULL(MIN([ID]))

    Inputs and Setup:

     

    [ID]: has the set of values to be tested. As of Tableau v9.2 this field can be of any data type, prior to that Boolean values would require a different calculation.


    Comments:

     

    Alternatively, in many datasources a test like COUNTD([ID]) > 1 may accomplish the desired result, though COUNTD() will only return 1 for a combination of a single value and Null. The seed for this test was originally based on Joe Mako's post at http://community.tableau.com/thread/108930 though the original has been significantly updated.

     

    Updates:

     

    20181006 Jonathan Drummey - created a simpler version of the multiple values test & boolean test, added more details on what ATTR() is and how it can be used, updated the attached workbook to v9.0.

    20181028 Jonathan Drummey - cleaned up some descriptions