6

I have a dataset that looks similar to this:

enter image description here

As you can see some ABNs have multiple business names attached while others have just one. I want to create a filter that allows the user to see just those rows where there are multiple names to each ABN. For example, a filer that will filter out ABN 456.

I can imagine how I would write that in SQL, but cannot get my head around how to do it in Tableau. Any help would be appreciated.

serv-inc
  • 29,557
  • 9
  • 128
  • 146
user2343837
  • 965
  • 4
  • 19
  • 30

1 Answers1

6

I believe the easiest way to do that is through a conditional filter.

1) Drag ABN to filter

2) Go to the Condition tab

3) Select By field, Number of records, Sum, >, 1

That way you're filtering ABNs that have more than 1 record. If you have more granular data (e.g. different classifications of the amount), and Number of Records is not exactly what you're looking for, you can use BUSINESS NAME, Count(Distinct). That is bullet proof for what you need (though a little slower)

Inox
  • 2,225
  • 3
  • 11
  • 25
  • 2
    Inox hit the nail on the head. If you are familiar with SQL, it might help to realize that when you use the condition tab when filtering a dimension, it is equivalent to generating a HAVING clause. The general tab corresponds to a WHERE clause, and the top tab corresponds to a LIMIT and ORDER BY combination. They don't guarantee the driver generates exactly that SQL, but that's the general breakdown. As a side note, COUNT([Number of Records]) can be slightly more efficient than SUM([Number of Records]), though its seldom noticeable. – Alex Blakemore Aug 16 '14 at 17:39
  • And when you put a measure on the filter shelf, the difference between whether it leads to a WHERE clause or a HAVING clause is driven by whether you are filtering all values (no aggregation function) or filtering an aggregate function. .i.e. filtering on Sales will lead to a where clause, while filtering on Sales will lead to a having clause. – Alex Blakemore Aug 16 '14 at 17:54
  • Interesting, I would think SUM([Number of Records]) would be faster than COUNT (but surely not faster than COUNTD). THanks for the information – Inox Aug 18 '14 at 12:10
  • A database can sometimes calculate COUNT() from an index, which may be in memory, while SUM() requires looking at data values. Not sure if it matters for a numeric literal 1 like [Number of Records]. Tableau used to have COUNT() be the default aggregation for [Number of Records] then switched to SUM(). Not sure why. COUNTD() is usually expensive comparatively -- can require sorting the results unless the column is indexed. Take these comments with a grain of salt & check the query plan for the particular database with the particular set of indices. – Alex Blakemore Aug 19 '14 at 00:01