BI Stories

Author: dchissick

  • Why Tableau ?

    Why Tableau ?

    I’ve been working as a consultant for a Tableau Partner (and reseller) for over a decade, so I’ve been asked by potential customers many times: Why Tableau? Why should we prefer Tableau over its competitors – Power BI, Qlik, Looker, and various others?

    For the customers we have various answers, depending on the context – their requirements, size, deployment type, self-service, embedding, even the community support, and more. But why am I a Tableau “freak”? And so many others in this same community? What makes Tableau special, compared to the other top-end visualization tools?

    I’ve been working in data visualization since the nineties, first with Panorama (which, back then, was excellent) and then touching upon various other tools. When I tried out Tableau for the first time (around 2014) it was just a test in my spare time, and nothing happened. But about a year later I got my first project, so I had to dig deeper, and I was hooked.

    So what’s the difference? There are two major factors.

    First – all the visualization tools I had used before, and most of those I’ve seen since, have a similar method for creating a chart (“worksheet”, “widget”, or whatever): You choose the type of chart from a menu, connect it to your data model, and start setting the properties – which dimension is on rows (or “categories”), what measure to display, bar color, line thickness, label font, and so on. If you need a different type of chart, you get a new set of properties.

    Power BI chart selection
    Quicksight charts

    Tableau, from the beginning, was different. Every worksheet (for a chart or table) uses the same set of definitions, or cards: Columns, Rows, and Marks. You have several types of marks, but almost all of them have the same sets of properties – Color, Size, Text, Tooltip, Detail (and sometimes Shape, Path and Angle). The “Show Me” pane is just a set of default configurations, not really a chart selector, and I rarely use it.

    This means that once you understand the interaction between rows, columns and marks, you can create almost anything – and I mean anything, not just data visualizations, but also artwork and games, for example. There’s an inherent flexibility that gives Tableau an advantage over other tools, both in speed of development and in the ability to iterate and “play around”, because you don’t have to select or switch the type of object that you’re working on all the time, and you’re not limited to their predefined attributes.

    The Tableau interface

    The second factor is Tableau’s calculations. I agree that all BI tools have the ability to create calculated fields, but Tableau has a great combination of a simple interface – everything is in one place, easily accessed and edited – and a large array of options, from the simplest arithmetic to Level of Detail functions and Table Calculations. Once you gain a basic understanding of how it works – aggregate and row-level, and a few other basics – it’s very easy to use, and also very powerful. 

    Try creating this without Tableau

    Some people don’t get it. They’ve been using Power BI or another competitor’s software for years, have difficulty switching to a Tableau mindset, and will always prefer their original tool. But I believe that Tableau’s greatest advantage is still the basic development interface, that allows you more flexibility and speed of implementation compared to its competitors.

    Of course there are other features. In Desktop – you can create a flexible data model from almost anything, and then manipulate the data in various ways. Dashboard design and actions. Beyond it – Tableau can be used by a lone researcher or by an enterprise with 50,000 users, online or offline. Tableau Public, of course. APIs, embedding, and more. The Community.

    But as Andy Kriebel, in my opinion the greatest Tableau guru of all time, recently wrote:

    Tableau is not Agentic Analytics
    Tableau is not Tableau Next
    Tableau is not Tableau Cloud

    Tableau is Tableau Desktop

    What he meant was that the core of Tableau is still the basic development interface, of which Tableau Desktop is the main component. You can add features around it, but without Desktop it won’t be the same. And Desktop is what makes Tableau the best.

  • DATETRUNC

    One of the most underrated functions in Tableau, in my opinion, is DATETRUNC. Underrated, underused, and not understood. Recently I was disappointed to read a whole book about Tableau in which it wasn’t mentioned even once.

    Technically speaking, DATETRUNC “truncates” any date value to the starting point of the period: year, quarter, month, week, and so on. For example:

    NOW()29/08/2025 16:14:35
    DATETRUNC(“hour”, NOW())29/08/2025 16:00:00
    DATETRUNC(“day”, NOW())29/08/2025 00:00:00
    DATETRUNC(“week”, NOW(), “monday”)25/08/2025 00:00:00
    DATETRUNC(“month”, NOW())01/08/2025 00:00:00
    DATETRUNC(“quarter”, NOW())01/07/2025 00:00:00
    DATETRUNC(“year”, NOW())01/01/2025 00:00:00

    This is useful for calculations, but the real power comes from understanding that DATETRUNC is a hierarchical function, because it actually returns the parent of the date at the given level. So if we want to check if [date 1] and [date 2] are both in the same month – meaning that both have the same parent month – we can use   DATETRUNC(“month”, [date 1]) = DATETRUNC(“month”, [date 2])

    … instead of what I have seen too many times:  
    MONTH([date 1]) = MONTH([date 2]) AND YEAR([date 1]) = YEAR([date 2])

    If we want to group dates by month, we can use DATETRUNC(“month”, [date]), and that’s it – we’ve created a parent field for [date] at the month level.

    This is especially useful in creating relationships for date fields in data sources, for example when one table is at the timestamp level (transactional data) and the other at the quarterly level (quarterly goals). Just create a relationship calculation with DATETRUNC(“quarter”…) on both sides, and it works.

    There are countless other examples, but the important thing is to understand the main idea: DATETRUNC doesn’t just change the date value, it raises it to a higher level in the hierarchy.

    Now go out and use it.

  • Workbook Locale

    Many times, I’ve encountered Tableau developers struggling with the date formatting in their workbooks, mostly as it defaults to the US format (mm/dd) instead of what they need – and then they waste time setting custom formats for date fields in various worksheets.

    The solution to this, and one of the lesser known features of Tableau Desktop, is the “Workbook Locale” setting, located under the “File” menu.

    The “Automatic” option defaults to your computer’s regional settings, but if you select “More” you can choose your language, and then all date and number formats in the workbook will use that setting by default.

    Even less known is the fact that this setting, at the workbook level, overrides any other regional locale. So if you save the workbook with a locale other than “Automatic”, the date and currency format is fixed, even after publishing to Tableau Server or Cloud.

    The order of precedence is listed below, and documented here:

    1. Workbook locale (set in Tableau Desktop)
    2. Tableau Server User Account language/locale settings
    3. Web browser language/locale
    4. Tableau Server Maintenance page language/locale settings
    5. Host computer’s language/locale settings

    The bottom line – you can control everything using Workbook Locale, so use it, unless you need varying formats for users in different languages or countries, of course.

  • The Measures Pivot

    The Measures Pivot

    Tableau shows great flexibility in creating and displaying measures, with a built-in “Measure names” dimension that has some of the characteristics of a regular dimension, but not enough of them. I have encountered several use cases for a “real” Measures dimension:

    • Enabling the user to select a set of measures to be displayed in a table or chart.
    • Creating groups of measures, like a hierarchy, for selecting or even aggregating values.
    • Calculating a Balanced Scorecard for multiple measures.

    In one case, a customer needed a scorecard based on 50-60 calculated measures, grouped by subject, with different threshold values for each measure, and a logic for aggregating the scorecard results up to the subject level. Technically it might have been possible to implement this using calculated fields, but “The Pivot” was our solution, and it worked!

    Let’s develop an example using (of course) Superstore data. I’ve created a number of calculated fields, and these will be my measures, or KPIs:

    I now have to create a KPI table, with a list of my KPIs (measures) and some supporting properties. My table is in Excel, but of course it can be a database table as well. A simple table would look like this:

    I now add this table to my data source, using a 1=1 relationship to link it to the base (fact) table. Note that the relationship between the tables has the same fixed value on each side, so technically it is a full cross-join:

    There is no need to be alarmed by the cross-join, which could theoretically create a cartesian multiple between the tables. Relationships in a Tableau data source are activated only when called upon by the analysis, and we will see how to do that selectively in a moment.

    The next step is to add a new calculated field. I’m going to call it “KPI Value”:

    The calculation takes each row in the KPI table, and links it to a different measure, or calculated field. All the calculations have to be aggregated, otherwise you will get the dreaded “Cannot mix aggregate and non-aggregate…” error, but you can use actual calculations as well as field names, such as:

    WHEN “S” THEN SUM([Sales])

    Now I have a measure called “KPI Value”, controlled by a “KPI Name” dimension, which can be filtered, grouped, or otherwise manipulated just like a normal dimension (though totals across this dimension are probably meaningless). For example:

    OK, but this looks weird. The measures are on totally different scales, so percentages appear as 0 or 1. We need to format the values, and this is where the “Format” field in the table comes in handy. In the example I have three options – “N”, “D”, “%” – but you can use as many as you need. Two additional calculated fields give us a formatted text value for each measure, that can be dragged to Label or Tooltip as needed:

    And the end result is:

    Note the ability to use “Group” as a hierarchy or a filter.

    Great. This is very useful, but there’s another level that we can add here – setting threshold values for all the KPIs, within the KPI table. I am leveraging the existence of this table, and adding a few more fields:

    The “Color” field is just a text description of a color, or state (the contents could also be “Good” or “Bad”, for example), but the “Color from” and “Color to” fields define a range of values for the KPI, that we can then color by the “Color” field.

    (I know, too many meanings of the word “Color”. But it’s worth it…)

    To implement this in our worksheet, I added one new calculated field:

    This filters the rows for each KPI so that only one “Color” row remains, and also takes into account those with no threshold values and just one row. I can drag it to the Filters card, filter by True, and drag the Color field to Color:

    And that’s already a type of Balanced Scorecard, highlighting measures (KPIs) by their performance. Any changes to the thresholds can easily be made by updating the KPI table, and the purists will split it into two tables, KPIs and Thresholds, with a join between them using KPI Code.

    This is Tableau, of course, so it can get even more complicated – and powerful. My original customer had three tiers of “Accounts”, with different threshold levels for each, so I simply added another level into the KPI table, multiplying the number of rows by 3 again. But it worked.

    A word of warning: this is a great technique, but it’s not good for performance. The original use case had 300+ rows in the KPI table, and less than a million in the raw data, and performance dropped to 30+ seconds per view, but the analysis was so powerful that my customer was still happy with it. So use it with care, and don’t expect something that works instantaneously with Superstore data to be as fast with tens of millions of rows and 40-50 KPIs.

    Note: the technique has also been tested with Tableau’s new multi-fact relationship model, and it works. There are two important considerations:

    • Link the KPI table to one of the fact (base) tables, and not a dimension table.
    • Each KPI calculation should be based only upon measures from a single fact table, otherwise results can be unexpected. That’s because the relationship model won’t know how to join the two tables before performing the calculation.

    The workbook that I used is published on Tableau Public, and the KPI excel file is below:

    KPIDownload
  • Tooltip = Axis ?

    Tooltip = Axis ?

    Tableau has many little quirks. With time you get used to them, but for new developers some of the small stuff can be very frustrating at first, and a nudge in the right direction always helps. So here’s one of them.

    In Tableau charts, any numbers appearing in tooltips are formatted using the “Axis” format, and not the default “Pane” format. So if you want your chart label to show “24.1%”, and the axis to have 0%, 5%, 10%, etc., your tooltip will show “24%”, which is a bit strange. See the example below:

    The best solution in such a scenario is to duplicate the relevant field, so basically you’re using two different fields – one for the label and axis, and another for the tooltip. Now you can set the axis format for the tooltip field (“Profit ratio (copy)” in the example below) without causing your axis to show unnecessary digits.

    Is there a reason for this functionality? Logic says that the tooltip format should be similar to the label, not the axis (which is usually more “rounded”), but maybe there’s something hiding behind it. And there’s been an Idea (now on the Salesforce IdeaExchange) about changing it for 12 years…

  • Tableau Cloud 💔Static Files

    A nice feature of Tableau (Server) is that you can create data sources with multiple connections, including to files – for example a few database tables, joined/related to a static Excel file (because there’s a small set of data that’s not in your DB), or maybe to a shapefile.
    Then you can publish the data source, check the “Include External Files” box, and when refreshing your extract (or connecting live), the file is simply there. Static.

    Database tables with a relationship to Excel
    The Publish Data Source dialog box

    But what happens when you publish the same data source to Tableau Cloud?

    It turns out that this doesn’t work. The documentation states very clearly that it should work:

    But after testing thoroughly, and opening a case with Tableau Support, I can confirm that this causes an error on Tableau Cloud.
    If you publish a refreshable/live data source with a static file included, even after checking “Include External Files”, any attempt to connect or refresh extracts returns an error, as Tableau Cloud tries to access the file(s).

    This happens both with direct database connections and with Tableau Bridge. Now, obviously, from a technical point of view you can use a Bridge connection to connect to the file on a UNC path, but probably that’s exactly what the developer of the data source was trying to avoid.

    Why is this an issue?

    One of my customers is migrating from Tableau Server to Cloud, and has dozens of data sources that include static files. They discovered the problem only after trying to refresh the extracts on Cloud. All of them now have to be modified – mostly by loading the file into a database table.

    This issue is a major difference in functionality between Server and Cloud, but it is undocumented and doesn’t appear in any migration guides. So it’s important for the community (that’s you – my readers) to know about it, and take it into account for future migrations, at least until the documentation is corrected.

  • DZV is great, but…

    DZV is great, but…

    Dynamic Zone Visibility (or DZV) was introduced by Tableau back in 2022, and is a great feature. It enables you to display or hide any dashboard object, or container, based on the value of a parameter, or a calculation that uses a parameter.

    What most developers I’ve worked with don’t know, however, is that hiding a worksheet using DZV does not prevent Tableau from retrieving the data for the worksheet. So, for example, if you are using a parameter to switch between 5 different displays (so 4 are hidden), the data for all of them is being calculated every time you refresh the dashboard, or change a filter value, even if only one is visible. That’s a x5 performance hit!

    In order to test this thoroughly, I created a workbook with two worksheets: “All Routes”, which is quite slow, and “Bus Calendar”. I also created a parameter with two values (“Times”, “Map”) for switching between them, and the necessary calculated fields:

    Parameter
    One of the boolean fields

    I then created three dashboards:
    1. A dashboard displaying both sheets, with no DZV.
    2. A dashboard switching between both the sheets, using only DZV.
    3. Like no. 2, but adding a context filter on each sheet, using the same boolean field as the DZV, so the data is filtered out when the sheet is hidden.

    Filtering on the “Show map” field
    Context filter

    I then used a Performance Recording to see what happens under the hood. Note that Tableau uses caching, so when a worksheet’s data has already been retrieved using a specific filter, it won’t execute the query again. The results are below:

    DZV Performance Recording

    So what happened?

    • I opened the filtered DZV dashboard first, with “Times” selected in my parameter. Only the “Bus Calendar” query was executed.
    • I changed a filter that affects both sheets. Again, only the “Bus Calendar” query was executed.
    • I switched my parameter value to “Routes”. You can see in the screenshot above that only the “All Routes” query (the long green bar) was executed.
    • Now I opened the unfiltered DZV dashboard, changed the filter, and it immediately executed the queries for both worksheets, even though only “All Routes” was visible.
    • Lastly, I opened the dashboard that displays no sheets. No queries were executed, because both sheets already had the data.

    Obviously this is just a quick scenario. I’ve checked this much more thoroughly on both Desktop and Server, and you can easily check for yourselves using Tableau’s Performance Recording tools (more about that in a future post).

    For now, I’m not telling you not to use DZV. It has great advantages over the old “hack” of filtering worksheets (which I used here), in that you can hide other objects as well, and you don’t need to hide the worksheet title in order to make it disappear. Just bear in mind that hidden worksheets are still calculated, and that affects performance, especially if you have a lot of data.

  • Filtering Multiple Data Sources

    Filtering Multiple Data Sources

    This is a revised copy of my original post in the Tableau Community blogs from 2024.

    Back in the day (before 2020.2), you could add worksheets from different data sources to a dashboard, define a filter from one of them, “Apply to selected sheets”, and it would automatically filter the other sheets using blending.

    Then came relationships, which enabled us to create more flexible and efficient data sources. However, the filtering option was suddenly lost, because the secondary data source in a blend cannot include a relationship. When you try to apply a filter from one worksheet to another, and both are based on relationships, the second is suddenly disabled (grayed out or empty), and when diving into it you see the dreaded message:

    So we started creating workarounds – and especially using parameters as filters, with all their disadvantages: no “All” option, no multi-select, no “relevant values”. And telling customers (or users) that on dashboards using multiple “advanced” data sources their filtering options were limited.

    And then, after almost 4 years of dithering around this, I had an epiphany. Looking around a bit (using mostly Google Search, of course) I couldn’t find anyone who had implemented this sort of solution, or at least written about it.

    So here are the sample workbook:

    https://public.tableau.com/app/profile/dan.chissick/viz/RelationshipFiltering/SalesDashboard

    and the explanation:

    The data is based on the Microsoft AdventureWorksDW database. There are two data sources with relationships to various dimensions: Internet Sales and Reseller Sales. Both have similar data and some common dimensions.

    Basically the idea is to create a supporting data source for each dimension used as a filter, with no relationships. Joins are possible, of course – if you want to create a hierarchy, for example.

    In this case we have three supporting data sources:

    ·        Sales Territory (including a hierarchy of Group, Country, Region)

    ·        Product Category (including Category and Sub-Category)

    ·        Dates – this actually contains all dates within the relevant data range, and is based on a separate view from the actual data. I named the date field “Order Date” because this is the field’s name in the main data sources.

    Now create a dashboard and add worksheets from the two main data sources. I’ve added two bar charts.

    For the Sales Territory Country filter (for example), create a worksheet with only Sales Territory Country on the rows. Add the same field as a filter as well (select “Use all” for now).

    Go to your dashboard, and drag the worksheet onto it. Then make it disappear: set “Outer padding” in the layout to zero, and the height and/or width in pixels to 1.

    In the worksheet menu select Filters –> Sales Territory Country, and the filter will appear. Place it wherever you want, Apply to Worksheets –> Selected Worksheets, and select the worksheets with the data.

    And there it is – the filter affects both bar charts, using the Sales Territory data source, which has no relationships, as the secondary data source for the filter.

    Using the Product Category data source and another hidden worksheet, I created a couple of additional filters for Category and Sub-Category.

    As for the dates, the process is the same, and I can use any type of date filter – relative, range of dates, year, etc. It’s important for the independent Dates data source to include all possible dates in the actual data sources, so the blending works properly.

    To summarize – the finished dashboard has charts from two different data sources with relationships and multiple logical tables, filtered by common dimensions with little effort. The supporting “dimension” data sources can be re-used, so this is a relatively simple workaround for organizations working with many complex data sources and seeking to combine them within multiple dashboards.

  • Find in Data Pane

    Tableau always release new versions with a list of new and exciting (or not so) features, such as this, but there are sometimes really helpful additions or modifications that are unlisted, and even undocumented.

    I always start using a new version of Tableau Desktop almost immediately, because as a consultant I have to keep up to date. So when version 2025.2 came out I was working with it within a day, and suddenly saw a new menu entry:

    "Find in Data pane"

    My first reaction was “Is this what I think it is?”.
    My second was to click on it.

    It’s definitely what I thought. No more searching for a field from your worksheet in the Data pane, in order to check the formula. Click on “Find in Data pane” and it finds the field immediately.

    For me, as a consultant, this is a game-changer. I spend hours with my clients, when something isn’t working as they expect, checking their calculated fields, and we always start from the worksheet rows, columns, and marks. Now we can save time and jump straight to the field, without scrolling or typing in the search box.

    Unfortunately this is currently relevant only for Tableau Cloud users, because Server doesn’t have a 2025.2 version, but it’s a great new feature. And I haven’t found it in the documentation yet…

  • Click on “Use Extract”

    One of the infuriating behaviors I encountered early on with Tableau was when I was working with an extracted data source in Desktop. I would be creating worksheets, calculated fields, and dashboards, and then I needed to check something in the data source.

    So I went to the Data Source page, dug into the structure, found what I wanted – but I didn’t change anything. However, when I wanted to return to my worksheet, Tableau decided that something had changed, and started the dreaded extract refresh…

    What do you do in this situation? Wait 10 minutes for it to finish? Cancel and remain stuck in Live mode?

    There’s a solution:

    Cancel.

    If the worksheet starts processing in live mode, Cancel again.

    Now go to the data source’s right-click menu, and check the “Use Extract” option. Your extract still exists, so you are returned instantaneously to extract mode as if nothing has happened. Problem solved.

    I’ve seen this happen literally hundreds of times, especially while working with others, and most developers think they have no option except wait for the extract to finish. So I hope this small tip helps someone…