Last week I’d promised to explain why the solution for identifying whether All items in a Tableau Quick Filter were selected wouldn’t work under certain circumstances in Tableau version 8, here it is, and along the way I’ll explain why COUNTD(Customer Name) could be red and the “Cannot blend the secondary data source because one or more fields use an unsupported aggregation.” warning message.
Tableau v8 introduced a new style of data blending. In Tableau version 7 and earlier, “Data Blending 1” was based on three factors:
- The relationships between dimensions that Tableau would automatically determine.
- Any customization we had done to the relationships via Data->Edit Relationships…
- The dimension pills in the view, i.e on Rows, Columns, Pages, or Marks Card.
For “Data Blending 2” or “DB2” in v8, data blending gets more complex (in a very useful way):
- The relationships between dimensions that Tableau would automatically determine.
- Any customization we had done to the relationships via Data->Edit Relationships…
- The dimensions that have linking turned on. These dimensions may or may not be in the view, and a related dimension that is in the view can be turned off.***
However, the new style of blending does not support all computations in all cases, in particular the “non-additive aggregates” of COUNTD(), MEDIAN(), and RAWSQLAGG_xxx() are not usable when one or more linking dimensions are not in the view.
When I first came upon this, I honestly thought Tableau was broken, and so have others on the Tableau forums, because it’s a subtle issue. Our COUNTD(Customer Name) will work in one situation and not another. Here are a couple of examples:
Set up a view with Coffee Chain as the primary and Superstore Sales as the secondary, using Market from Coffee Chain as Region in Superstore Sales, with COUNTD(Customer Name) as one of the measures. By default, any fields with the same name in both data sources are turned on as linking field when we bring them into the view:
Then, let’s say we just want one set of results from Superstore Sales, so we drag Region off the view and get the “Fields cannot be used…” warning message:
To attempt to get rid of that message, we turn the Region linking field back on and then COUNTD(Customer Name) and MEDIAN(Profit) both go red and the view turns grey:
Or, let’s say neither COUNTD(Customer Name) nor MEDIAN(Profit) were in the view when we did steps 1 and 2, and then we’re dragging COUNTD(Customer Name) into the view. The pill turns red and the view goes grey:
If we hover over one of the red pills, we get an error message saying “Cannot blend the secondary data source because one or more fields use an unsupported aggregation.”
Explaining Unsupported Aggregations
When we set up a data blend, Tableau is issuing queries to both data sources. Here’s
a basic blend with the linking field in the view:
Here’s a simplified version of what the queries look like. If you’re not familiar with SQL, there’s a nice tutorial at W3Schools.
//query for the primary Coffee Chain SELECT [Region] FROM [CoffeeChain] GROUP BY [Region] //query for the secondary Superstore Sales SELECT [Region], SUM([Sales]) FROM [Superstore Sales] GROUP BY [Region]
This is DB1 style blending. The related dimensions that are in the view are in the GROUP BY clause for the secondary datasource and the non-additive aggregates are then computed in the query at the appropriate level of detail.
Now let’s turn on a linking field in the view, like State:
The queries will look something like this:
//query for the primary Coffee Chain SELECT [Region], [State] FROM [CoffeeChain] GROUP BY [Region] //query for the secondary Superstore Sales SELECT [Region], [State], SUM([Sales]) FROM [Superstore Sales] GROUP BY [Region], [State]
This is the new DB2 style blending in action. Tableau puts all active linking dimensions into the GROUP BY clause in the query. Since the view itself is at a different level of detail (just having Region on Rows), once Tableau has internally joined the two data sources it then does another computation to re-aggregate the data at the view’s level of detail.
In other words, the level of detail in the query and the level of detail in the view can now be different in version 8.
Where this runs into an issue is with those non-additive aggregates of COUNTD(), MEDIAN(), and the RAWSQLAGG_xxx() functions. With the linking dimensions in the view (i.e. DB1-style blending), the queries look like this when COUNTD(Customer Name) is in the view:
//query for the primary Coffee Chain SELECT [Region] FROM [CoffeeChain] GROUP BY [Region] //query for the secondary Superstore Sales SELECT [Region], SUM([Sales]), COUNTD([Customer Name]) FROM [Superstore Sales] GROUP BY [Region]
If we tried to add State as a linking dimension that’s not in the view, though, we’d be asking Tableau to do something like this:
SELECT [Region], [State], SUM([Sales]), COUNTD([Customer Name]) FROM [Superstore Sales] GROUP BY [Region], [State]
And then Tableau would have to re-aggregate that along Region. But to get an accurate count distinct of Customer Name along Region, Tableau needs the list of Customer Names for each Region, which isn’t returned by the query. Rather than return incorrect results, Tableau turns the pill red, greys out the view, and generates the unsupported aggregation message.
The workaround for this is that if you have to use one of the non-additive aggregates, use DB1 blending, i.e. keep the dimensions in the view. If that increases the level of detail too much for your aggregation to work, then at least for COUNTD() and MEDIAN() you can use TOTAL(COUNTD()) or TOTAL(MEDIAN()) with the right Compute Using to generate an accurate result.
If you’d like Tableau to implement support for non-additive aggregates in blends, vote for it at http://community.tableausoftware.com/ideas/2250. The workbook used for generating the screenshots is at http://public.tableausoftware.com/views/countdcustomername/Dashboard.
Another elegant clarification to a struggle I was having. Thanks!
This is a brilliant explanation, thank you so much.
One question:
In that 2nd to last paragraph you say that for COUNTD() you can use TOTAL(COUNTD()) with the right Compute level… Is that still with blended data? I’m feeling a little slow, but I haven’t been able to get that suggestion to work.
The TOTAL(COUNTD()) will work with blended data, but currently you must use DB1 blending, in other words you need to have all of the linking dimensions actually in the view (on Pages, Rows, Columns, or Marks Card). This gives Tableau enough detail to perform the computation. Does that make sense?
Got it, thanks Jonathan, that makes perfect sense
Great!
One simple question: how did you manage to not having a grey cilinder with orange chech mark on it for Region pill on Rows shelf? I mean that secondary data source pills being placed on a view should have those marks on it, shouldn’t they?
Hi Egor, there are two different Regions we can use: Region from the primary data source (which won’t have the blend icon aka the grey cylinder with orange check mark), or Region from the secondary source (which will have the blend icon). When I’m working with data blending I always try to use dimensions from the primary data source, they are “first class” dimensions and will always affect the level of detail of the view, whereas dimensions from secondary sources are dependent on what’s happening in the view. Does that help answer your question?
But there’s no Region field in Primary data source (Coffee Chain) – only Market one!
P.S. Oops, I think you just renamed Market to Region in Coffee Chain for simplifying setting relationship. Am I right now?
In one way, yes. In the workbook I didn’t rename Market, I created a calculated dimension named Region that points to Market, and Tableau is smart enough to pick that up in data blending. I typically do that (keep the original dimension & make a calc field for the blend) when field names are different so that way if/when I have to go back to the underlying data, I have a more obvious trail to follow to find out what the field name is.
Thank you, Jonathan, for the hint and explanation!
You’re welcome!
I am still struggling with the Total(countd()). For example, lets say you wanted to count unique members in a data set, ie field named Member ID. Would you put total(countd(member id))?
Hi Robbie,
The answer depends on the level of detail (grain) of the view. If you don’t have any dimensions in the view then all you’d need is COUNTD([Member ID]). If there are dimensions in the view and you want to count uniques then you’d need to use a table calculation like TOTAL(COUNTD([Member ID])) with addressing on all dimensions in the view, a level of detail expression like {FIXED : COUNTD([Member ID])} (though be careful of filtering, see https://www.tableau.com/learn/whitepapers/understanding-lod-expressions for details), or a data blend that could use the regular aggregate COUNTD([Member ID]).
Hi
I am facing the same issue where i need to calculate Total no of orders.
it should not change when use region as Filter.
Getting error with this
{FIXED : COUNT([Order ID])}
Hi Pavan, in this case I’d suggest using an additional connection with an additional data blend that does not include the region filter.
Hi,
I am new to tableau started learning. As I was going through the documentation in blending at troubleshooting blending I came across this issue with non-aggregate limitation, got this article. Thank you very much for detailed explanation, until this I can understand what happening behind the scenes.
I downloaded the workbook and played around it, I have few doubts and observations
1) I didn’t understood this line ****and a related dimension that is in the view can be turned off.*** from factors of blending in DB2
2) Can I achieve DB1 using the current version of tableau.
3) While following the comments section, I have played with the workbook the point raised by Egor Ushakov I have also had the doubt when I saw the image at first instance. As played around the region pill taking off the shelf and trying it to link again from the secondary data source countd and median been red and screen turned to grey. After a while I realized am not using the region from the primary data source. After bringing the region from the primary data source it works. I don’t know every thing worked fine after bringing the region pill from the primary source. Adding the region from secondary source didn’t effect the view.
4) I didn’t understood about the total(countd(dimension)).
Thank you.
Hi Rathnam,
Sorry for the delay in replying, comments on my site weren’t working properly for awhile.
1) When we add fields from secondary data sources to a view Tableau “joins” the data as part of the blend on one or more linking dimensions. We can see these by activating secondary data source and looking at the dimensions window, the linking dimensions will have an orange chain icon. Those linking dimensions can be turned on and off with a click and n versions of Tableau prior to v8 we couldn’t control blending in that way.
2) Yes, I’m sorry that wasn’t more clear. In fact Tableau defaults to using DB1 blending until one of the conditions for DB2 blending is met.
3) I’m not totally clear on your question…if it’s about seeing different behavior than what I’d posted one factor that can create a difference in behavior is your Tableau version. I wrote this post when v8 came out, there have been 12 major releases of Tableau since then Tableau has made some changes to support COUNTD(), MEDIAN(), and PERCENTILE() in a few more situations than were originally supported (usually with text & excel files & extracts).
Thank you, for the explanation!