QueenOfData

Filtering dimensions without losing RANK in Tableau

You are ranking your items using the table calculation of RANK. You wish to filter on a subset of these by a certain category. As soon as you do, Tableau recalculates the rank, but you wish it to stay as it was. How can you filter dimensions without losing rank? Read on for the answer.

Why we lose RANK

For this, we need to quickly delve into the Order of Operations (OOO). Jim Dehner has a fantastic explanation on how OOO works, which I strongly suggest you check out, but allow me to give you a quick overview here.

  • Extract filters and data source filters: these two limit the data that returns as an answer to the query Tableau sends to your data source. If you limit something here, Tableau physically will not hold that part of the answer to your question.
  • Context filters and dimension filters: these two limit the rows of data that Tableau will take into account in your analysis. The rows are there, Tableau simply will not look at them.
  • Measure filters: as measures aggregate the values of multiple rows, these obviously need to come after context and dimension filters. Tableau first aggregates the data, and then decides which batches of rows to not show in the analysis based on those aggregated values.
  • Table calculation filters: as table calculations are a form of aggregation on top of an aggregation, these obviously need to come after measure filters. Tableau aggregates multiple aggregates to one multi-level aggregated value. Then it decides which batches of rows not to show in the analysis based on those aggregated aggregates.

If you think about it, the order Tableau follows is pretty simple: physical data that is queried, single rows that Tableau will disregard before aggregation, batches of rows that Tableau will disregard after aggregation, and more batches that Tableau will disregard after aggregating the remaining aggregates.

As you can see, the RANK is an aggregate on top of an aggregate. If you change the rows that go into the first level aggregation by filtering on a dimension, the resulting aggregated aggregation (your RANK) must necessarily be changed by that filter.

How to circumvent this

If you want to filter without changing the results of a table calculation, you need to bring your filter to that same level: a table calculation. You can do this by using a LOOKUP function.

LOOKUP will return a desired value in a target row in a relative offset from the current row. The thing is, we don’t want a value from a different position, we want the one in the same row. We simply use an offset of zero.

As a table calculation needs an aggregated value to work its magic, we need to aggregate our dimension. Ideally, your rows will only contain one distinct value of whatever dimension you wish to filter on. In that case, you can use either MIN or MAX to create your table calculation. Sure, you could use ATTR as well, but that takes more power to compute, hence decreasing your performance.

Your calculation will look like this:

LOOKUP(MIN([your dimension]),0)

You will find the resulting field under measures, seeing as it is aggregated. Simply drag this field onto Filter and use it to filter your data. Your rank should now remain the same.

Find more tips like this one in my Tableau Tip Battle with Annabelle Rincon at the Tableau Fringe Festival of December 2020: