The world’s leading publication for data science, AI, and ML professionals.

3.3 arguments on deciding if using multiple Date tables is a good idea

Usually, we have one Date table in our Tabular Models. But sometimes, multiple date tables could be a good idea. Let's dissect why

Photo by Jens Lelie on Unsplash
Photo by Jens Lelie on Unsplash

Some time ago, I wrote an article on enhancing your reporting with an extended Date table. You can find the article here: 3 Ways to Improve your reporting with an expanded date table.

Since then, I have had discussions with Clients and Colleagues on why I would need more than one date table in my Tabular Models in Power BI or Analysis Services.

There are three topics to look at when we try to decide if we want to have only one date table or to have multiple Date tables:

  1. Usability
  2. Simpler DAX code
  3. Simplified Model

Let’s look at all three topics.

But first, we need to understand the "problem".

The Problem

Let’s look at an example in which we have transactions with multiple dates:

Figure 1 - Transaction table with multiple dates
Figure 1 – Transaction table with multiple dates

In the figure above, we see transactions with more than one date.

Now, we want to be able to create reports base on each of the three dates.

We can create either a simple model, like this:

Figure 2 - Simple data model with one date table
Figure 2 – Simple data model with one date table

The active Relationship links the OrderDate columns to the Date column.

Or we can build another data model with one date table for each date column in the Online Sales table:

Figure 3 - Data model with one date table for each date column
Figure 3 – Data model with one date table for each date column

The first, simpler Model, is the common one. This simple Model is helpful for the majority of use cases.

Anyway, there could be situations in which the Model with three date tables could be more helpful.

Let’s dig into the three arguments mentioned above on deciding which Model is better.

Usability

Usability is not objective, and it goes down to the users’ taste, which will use the data model.

When you’re developing your Power Bi model to create a Power BI report, then you can follow the path you like.

But when you’re developing a data model, which should be used by other users as well, then you need to consider their user skill, habit and understanding of the data model as well.

A shared data model could be through a shared Dataset in the Power BI Service or an Analysis Services data model.

I want to show you which parts of the data model can influence Usability:

  • The complexity of Measures
  • Calculation Groups
  • Understanding of the purpose

The complexity of Measures

When I have multiple date tables, I can create simple Measures. I can use these Measures with any Date table in the Model.

I can use this simple DAX Measure for Sales Amount with any Date table:

Online Sales = SUMX('Online Sales', [UnitPrice]*[SalesQuantity])

While, with only one date table and multiple Relationships, I need to use the USERELATIONSHIP() function to achieve the same functionality for one of the non-active relationships.

DAX Measure to calculate the Sales Amount based on the Due Date:

Online Sales by Due Date =
CALCULATE(
    SUMX('Online Sales', [UnitPrice]*[SalesQuantity])
    ,USERELATIONSHIP('Online Sales'[DueDate],'Date'[Date])
    )

But, when you have multiple Date tables, you can use the first, simple Online Sales Measure with all date tables without the need to add more Measures.

Moreover, when the user can select which date table he wants to use, he can just do it, and it’s transparent.

On the other way, the users will not be able to compare the number based on the different Date axis in the same Chart:

Figure 4 - Compare data from two Measures
Figure 4 – Compare data from two Measures

But, as soon as we need Time-Intelligence Measures, we must create the same amount of Measures.

Calculation Groups

We can use Calculation Groups to simplify our Model. We can use this feature to avoid redundant logic in different Measures.

You can read more on Calculation Groups in the SQLBI Article Introducing Calculation Groups – SQLBI

Unfortunately, there is no difference when using one or multiple Date tables. For example, when creating Time Intelligence Measures, you always have to develop various Calculation Groups for each date axis. Regardless if you use one or multiple Date tables.

Calculation Groups will not help you with the decision of which route you want to take.

Understanding of the purpose

It is crucial to name your Measures accordingly.

As you can see in the following picture, I added a Suffix to each Measure to define the purpose:

Figure 5 - Measure with Purpose
Figure 5 – Measure with Purpose

It would be best to do this independently from your decision to use one or multiple Date tables.

You have to keep in mind that your users need your help to understand the purpose of each object in your Model.

So, it would help if you name your Date tables accordingly, as shown in the Pictures above (Figures 2 & 3).

Simpler DAX code

Photo by Bench Accounting on Unsplash
Photo by Bench Accounting on Unsplash

In the previous section, we have seen that we need to write more code when we want to make sure that we calculate the Sales Amount based on a specific Date axis when we use one Date table.

The same is true when we write Time Intelligence Measures.

It’s not about the number of Measures. We always need to develop the same number of Time Intelligence Measures, regardless of our decision.

But we have to add a USERELATIONSHIP() to each Time Intelligence Measure when we have one central date table:

Previous Year Measure by Due Date with one Date Table:

Online Sales PY (Due Date) =
VAR SalesAmountPY =
    CALCULATE(
        SUMX('Online Sales', [UnitPrice]*[SalesQuantity])
        ,SAMEPERIODLASTYEAR('Date'[Date])
        ,USERELATIONSHIP('Online Sales'[DueDate], 'Date'[Date])
        )
RETURN
    SalesAmountPY

In contrast, look a the following DAX-Code to calculate the previous year, based on the Due Date, when we have a dedicated Due Date table:

Online Sales PY (Due Date) =
VAR SalesAmountPY =
    CALCULATE(
        SUMX('Online Sales', [UnitPrice]*[SalesQuantity])
        ,SAMEPERIODLASTYEAR('Due Date'[Date])
        )
RETURN
    SalesAmountPY

This solution is much easier to write and to read.

Sometimes, multiple date tables can help solve problems that could be very complex to solve when you have only one Date table.

In my Article A DAX drama in 3 acts with two time dimensions, I described a problem in which I needed to use two Date Axis to apply two different Date filters to my data.

I don’t know if the problem explained in the article above could be solved with one single Date table. And even if it could be solved, the solution would be very complex and challenging to understand.

You need to carefully consider the requirements and decide accordingly if you can use one Date table only or if you need multiple Date tables.

Simplified Model

The most obvious reason to have one Date table in your data model is to simplify your data model.

The following picture shows a data model with one Date table:

Figure 6 - Data Model with one Date table
Figure 6 – Data Model with one Date table

Now compare the same data model, but with multiple Date tables:

Figure 7 - Data model with multiple Date tables
Figure 7 – Data model with multiple Date tables

You see that the second one is larger and looks more complex.

But, when you look closely at the first Data model, you can see the three Relationships from the Online Sales table to the Date table. These three Relationships looks complex as well, to the untrained eye.

At this point, it’s your decision, which data model looks easier to understand or which one is the more complex data model.

Combining both solutions

Sometimes, the Requirements collide, and you cannot stick with one method.

In this case, you can create a model that combines both methods in one:

Figure 8 - Combined data model
Figure 8 – Combined data model

Now you have all possibilities, and you can use the best of both methods in your Model.

But the cost is the increased complexity and the danger that users will use Measures with the wrong Date table.

Imagine that you create Time-Intelligence Measures using the Ship Date table. But when a User creates a Report on this Model and adds the Date table to a report, the Measure will not work anymore.

Then you need to add checks to your Measure to find out which Date table is used in the Report.

Example 1 – Dynamic PY Measure with Check

Here an example of how you can do this:

Sales PY (by Ship Date) =
    VAR IsShipDate = ISFILTERED('ShipDate')
RETURN
    IF( IsShipDate
        ,CALCULATE([Sum Online Sales]
        ,SAMEPERIODLASTYEAR('ShipDate'[Date])
        )
        ,CALCULATE([Sum Online Sales]
            ,SAMEPERIODLASTYEAR('Date'[Date])
            ,USERELATIONSHIP('Online Sales'[ShipDate], 'Date'[Date])
        )
    )

Example 2 – Dynamic PY Measure with base Measure

Alternatively, you can use the SWITCH() function to create a more complex check to combine all Date tables in one Measure.

Here the base Measure to determine which Date table is used:

Get used Date table =
SWITCH (
    TRUE
        ,ISFILTERED('OrderDate'), 1
        ,ISFILTERED('ShipDate'), 2
        ,ISFILTERED('DueDate'), 3
        ,0
    )

Then you can use this base Measure in every other Measure, like the following:

Sum Online Sales PY (Dynamic) =
SWITCH([Get used Date table]
    , 1
    ,CALCULATE([Sum Online Sales]
        ,SAMEPERIODLASTYEAR('OrderDate'[Date])
    )
    , 2
    ,CALCULATE([Sum Online Sales]
        ,SAMEPERIODLASTYEAR('ShipDate'[Date])
    )
    , 3
    ,CALCULATE([Sum Online Sales]
        ,SAMEPERIODLASTYEAR('DueDate'[Date])
    )
    ,CALCULATE([Sum Online Sales]
        ,SAMEPERIODLASTYEAR('Date'[Date])
    )
)

When we are adding such logic to a Measure, we have to consider Performance as well.

I created a Matrix with Example 1 in Power BI and used the Performance Analyzer in Power BI Desktop to copy the Query for this Matrix to DAX Studio. I used the Server Timings Feature to collect the Timing information.

I then executed the query with on Measure at once to get meaningful measurements.

As a Baseline, let’s use the simple aggregation of Sales Amount:

Figure 9 - Baseline measurement (ShipDate table)
Figure 9 – Baseline measurement (ShipDate table)

Now the timing for the PY Measure with the ShipDate Date table:

Figure 10 - Measurement for PY with ShipDate table
Figure 10 – Measurement for PY with ShipDate table

Now, the measurement with the Measure, which performs the check of which Date table is in use:

Figure 11 - Measurement for PY with Date table check
Figure 11 – Measurement for PY with Date table check

To compare, here the same Measure, but this time using the Date table, which needs the USERELATIONSHIP() function:

Figure 13 - Measurement for PY with Date table check and USERELATIONSHIP
Figure 13 – Measurement for PY with Date table check and USERELATIONSHIP

Here all the timings in a table:

Figure 12 - Timings table
Figure 12 – Timings table

As you can see, the usage of the Relationship between the Online Sales and the ShipDate tables takes only 127ms more time.

The check for the used Date table takes only an additional 30 ms. This is very little time, and you don’t need to think about it.

But the use of the USERELATIONSHIP() function takes over 200ms, compared to the use of the Relationship.

Of course, these values depend on several factors of your Model, and you need to perform your measurements to get the proper differences in Performance.

Conclusion

Well, the Answer to the question "Should I use one single Date table or do I need multiple Date tables?" is:

It depends

Photo by Sharon McCutcheon on Unsplash
Photo by Sharon McCutcheon on Unsplash

You have to evaluate the Reporting requirements and the capabilities of your users.

My recommendation is:

  • Start with one single Date table, unless you know that you need dedicated Date tables.
  • As soon as you have a reason, change to dedicated Date tables

This approach worked for me very well until now.

But, why did I use "3.3 arguments on deciding if using multiple Date tables is a good idea" as a title for this article?

I used three main arguments:

  1. Usability
  2. Simpler DAX code
  3. Simplified Model

But inside Usability, I mentioned further three reasons:

  • The complexity of Measures
  • Calculation Groups
  • Understanding of the purpose

Hence, 3.3 Arguments.

If you appreciate my work, feel free to support me through

Salvatore Cagliari

Or scan this QR Code:

Any support is greatly appreciated.

Thank you.


Related Articles