Four Core Differences Between the Tableau and Power BI Data Models

Tableau recently released a logical data model in version 2020.2, while a similar semantic layer has been core to Microsoft Power BI since its inception. Power BI’s “tabular” model has a shared lineage across Microsoft products that predates Power BI itself (Power Pivot for Excel and Analysis Services have the same engine as Power BI).

Here is the same simple model defined in both Tableau (top) and Power BI (bottom):

Prior to 2020.2, Tableau had a physical data model that allowed joins between tables. This layer is still present, and it is similar to Power BI’s merge capability within Power Query to join multiple tables into a single table.

With the introduction of the logical model in Tableau 2020.2, a single logical table may consist of one or more physical tables. Two or more logical tables may be related to each other. This approach shares many similarities with Microsoft’s tabular model, but there are currently some core differences that may impact how you approach data modeling in one tool versus another. It’s not always going to be a straightforward scenario to apply knowledge of how to build a data model in one product to building it in the other.

NOTE: This post considers some core differences between Tableau and Power BI modeling as of June 2020. It does not go into detail on announced roadmap features such as Power BI’s enhanced composite models coming later in 2020, which will allow scenarios like combining a “live connection” composite model with additional data sources, or even multiple live connection models.

While not exhaustive, here are four core differences between the logical data modeling experience between Tableau and Power BI:

1) Multiple Fact Tables

While this may only be a sign of Tableau’s fledgling modeling capabilities, the logical model strangely does not support multiple fact tables joined to more than a single dimension.

In Power BI, it’s common to build data models with multiple fact tables that may relate to multiple dimensions like so:

I’m not sure how valuable a setup like this is (it’s not):

Tableau’s docs have an “unsupported models” section that offers possible workarounds such as merging fact tables together in physical tables. That’s not ideal but may sometimes work. This suggestion in the docs to possibly crossjoin dimensions should be avoided though: “Alternatively, you might be able to cross-join Date and Customer to create a single dimension table”. Out of love and consideration for anyone who needs to use or maintain your model, please don’t crossjoin all your dimensions. Hopefully Tableau supports more robust data models with multiple fact tables in a future version soon.

2) Defining Relationships with Multiple Fields

Tableau allows data modelers to define relationships on multiple fields. Power BI’s tabular model only allows relationships defined by a single field (and that will not change anytime soon).

In Power BI, if a relationship needs to be defined on multiple fields, that composite key must be built manually as a workaround by concatenating the fields together. For example, if you need to create a geography relationship based on country and state, you end up manufacturing a composite key with values like “USAMichigan” instead of relating on the Country field and State field separately.

Modelers who have been working with Microsoft’s tabular engine might not like it (that’s an understatement), but we’re used to it. It’s refreshing to see Tableau offer a convenient alternative. If anyone starts in Tableau and then needs to build a model in Power BI, enforced single key relationships might lead to some confusion.

3) Active and Inactive Relationships

Both Tableau and Power BI allow a single active relationship between tables. In addition, Power BI allows multiple inactive relationships between the same tables. Measures defined using DAX can utilize these inactive relationships and override the default active relationship when needed–which is not often.

A common scenario is multiple dates in a fact table. In Power BI, more often than not, you will see role-playing dimensions setup with multiple date dimensions instead of measures using inactive relationships (dotted line in the screenshot below). They are there if needed though while Tableau does not allow multiple relationships between tables.

4) Bi-directional Relationships

In a one to many relationship, Power BI allows filters between the one side to the many (single direction) and also from the many side to one (both directions). Outside of special cases, using bi-directional relationships is generally discouraged in Power BI as they may provide unintended results if you have more than one fact table in the model with common dimensions. There are occasional benefits to setting relationship direction to BOTH though on a limited basis.