Testing Whether ATTR() Returns * (Multiple Values)

Version 4
    Description:
    There are times when we want to know whether ATTR() is returning only one value or multiple values. ATTR() is a helper function that returns a value if there is one and only one value (and/or Null) of a set of values, and displays * (a special value of Null) when multiple values are returned. However, ISNULL(ATTR([ID])) only returns Null when all the values of ID being evaluated are Null, as we can see in the last of in the following 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).
    Example Calculation:
    // If all records are Null then there is only one value. This test is necessary
    // here because otherwise the following test can be erroneously triggered.
    IF COUNT([ID])==0 THEN 
        "Single"
    //First test is whether the comparison of the ID to a dummy string returns Null,
    //if so then multiple records have been returned
    ELSEIF ISNULL(ATTR(STR([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
        "Multiple"
    //Otherwise there is a single value
    ELSE
        "Single"
    END
    
    Inputs and Setup:

     

    [ID]: has the set of values to be tested. If field has a string data type, then the calculation can be optimized by removing the single call to the STR() function in the second test.


    Comments:

     

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