Don’t Be Scared of Calculations in Relationships


Calculations can be intimidating. Calculations in a data source that uses relationships can seem more intimidating. But there’s no reason to be scared of calculations in relationships.

Note: if you aren’t yet comfortable with the principles behind data sources that use relationships, it might help to read Don’t Be Scared of Relationships before diving into this topic.

Calculation types

A row-level calculation is something that is computed for each record in the data. For example, each sale transaction has a sales amount, the price, potentially adjusted by the discount percentage: Price * (1- ZN([Discount])). This is computed row-by-row for each transaction—the results can be thought of as adding a new column in the data source for Sales Amount.

There are also aggregate calculations. Aggregate calculations are computed at the level of detail of the visualization and their value depends on the structure of the view. Counts are an example of an aggregate calculation. The value of Count([Title]) depends on whether we’re looking at the count by format, author, or sales day.

See our blog(Link opens in a new window) for more information about types of calculations.

Table level of detail

Because each table in the Tableau data source can have its own level of detail, which table a calculation is associated with has a large impact. For example, historically in Tableau (pre 2020.2, before relationships), it was possible to count the number of records in the data source by creating a calculation with the constant value 1 and summing it. 1 was assigned to each row in the data source so the sum was equal to the number of rows.

Now, however, that calculation would have a value of 1. There is no global level of detail for the entire data source, and a constant calculation lives in the unassigned area at the bottom of the Data pane. It has its own level of detail. Summing 1 is simply 1.

As mentioned previously, calculations that have the same level of detail as a table—usually because they contain a field from the table—belong in that table in the Data pane. For example, the concatenation of First Name and Last Name to get the full name of each author (that is, [First Name] + " " + [Last Name])is placed in the Author table.

However, if we were to use fields from multiple tables in one calculation, the calculation would go to the unassigned area at the bottom of the Data pane. This is called a cross-table row-level calculation; it requires a row-level join of the tables involved, which can impact performance. (Make sure the correct cardinality is set in the Performance Options of the relationship).

Assigning the level of detail

Because the level of detail of a table controls what a calculation’s results represent, it’s important to have calculations in the appropriate tables. A FIXED Level of Detail expression can be used to pull a calculation into a specific table. The field the expression is fixed to (the dimension declaration(Link opens in a new window)) determines the level of detail of the results.

Example: Author book tours

Q: How many book tour events is each author participating in?

A: This number should “belong” to each author, so it should go in the author table.

  • Author Book Tour Events = {FIXED [Author Name] : SUM([Book Tour Events])}
  • (This can be thought of in plain language as “for each author, total the number of book tour events associated with them”)

Q: How many authors are not doing any book tours?

A: It’s tempting to try a calculation like

  • COUNTD(IF ISNULL([Book Tour Events]) THEN ([Author Name]) END).
  • (This can be thought of in plain language as “if Book Tour Events is null, then return the author’s name. Count each unique author name”)

However, this is a cross-table row-level calculation because Book Tour Events is from the Series table and Author Name is from the Author table. And cross-table calculations like that use inner joins, which means when there’s no corresponding value from both tables, the row is dropped from the join results. Which in turn means we’re asking Tableau to count something that doesn’t exist. Note that if the two fields were in the same table, however, this would be a fine way to do it. There’s nothing wrong with the calculation as it stands other than the fact it doesn’t play well with the structure of the data source.

Instead, we need to indicate the presence or absence of book tour events for each author—and keep those results in the Author table. Once each author is flagged as “participating in a book tour” or not, we can count the number who aren’t doing book tours.

  • Book tour participant? = IF {FIXED [Author Name]: COUNT([Book Tour Events])} = 0 THEN "No" ELSE "Yes" END
  • (This can be thought of in plan language as “For each author, count the number of book tour events. If that count is zero, label the author ‘No’, otherwise label them ‘Yes’”. By naming the field “Book tour participant?” the no’s and yes’s will line up.)

Now we can count the number of Nos and answer the original question.

If our calculation is Authors without book tours = IF [Book tour participant?]= "No" THEN ([Author Name]) END, we’ll get a list of authors who aren’t doing book tours. (This can be thought of as “for every author where Book tour participant? is no, list the author’s name.”)

If our calculation is COUNTD(IF [Book tour participant?]= "No" THEN ([Author Name]) END), we’ll get a numeric answer that will live in the unassigned area at the bottom of the data pane. Why? Because this calculation is aggregated.

Bonus Question: 

It’s also possible to use the structure of the Book tour participant? calculation directly to return the list of author names. IF {FIXED [Author Name]: COUNT([Book Tour Events])} = 0 THEN ([Author Name]) END . Where would this calculation go in the Data Pane? Why? Try it in your own copy of Tableau Desktop, or download this workbook to see. (Requires Tableau Desktop 2020.2 or higher. Once in the workbooks, cight click the calculation in the Data pane and select Edit to open the calculation editor and see an explanation in the calculation comments.)

Feeling a bit overwhelmed and want to go back a step? Try Don’t Be Scared of Relationships.

Ready to keep exploring how to do complex analysis with Relationships? Check out Don’t Be Scared of Deeper Relationships.

For more information on the technical underpinnings of relationships straight from the Product Management team, check out the series on relationships on the Tableau Blog.

Also see video podcasts on relationships from Action Analytics(Link opens in a new window), such as Why did Tableau Invent Relationships?(Link opens in a new window) Click "Video Podcast" in the Library(Link opens in a new window) to see more.

Back to top
Thanks for your feedback!Your feedback has been successfully submitted. Thank you!