Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calendar table performance

An in-memory calendar table can be created in many difference ways in Power BI. Here are a few:

1. Create the calendar table in the source system (e.g. SQL) and import it into Power BI.

2. Create the calendar table using M inside Power BI

3. Create the calendar table using "Enter data" in Power BI

4. Create the calendar table using "New table" and the using the DAX-function CALENDAR.

4.a. ...and add all additional columns (Year, Month, Quarter etc.) using DAX-function ADDCOLUMNS.

4.b. ...or add all additional columns (Year, Month, Quarter etc.) using "New Column".

 

Is there any performance difference between these alternatives?

 

(FYI I find the most convenient calendar table being alternative 4.b, because then it's easy to add/remove/modify the alternative columns without breaking relationships, "Sort by Column" etc. and it's also easy to clone the table including all columns by simply using "New table": CalendarB = Calendar A.)

 

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

First, thank you for sharing different ways to create a calendar table.

1.

the calendar table is static for way 1 and 3, for way1, to make the calendar table change, you need to change in data source and then refresh from Power BI Desktop.

So, way 3 is not recommended.

If you have a SQL Server data warehouse, you can use SQL to create date dimension tables.  It’s usually best to unify all of the reporting data in the data warehouse or data mart to create a single version of the truth for reporting.

 

2.Regarding way4

Obviously 4.b. is most recommended.

 

3.As for way2 and way4

It is hard to say which is better, They have their own advantages,

It is uncompariable, it depends on the your requirements.

We could take a look at the articles below:

Calculated table in power bi desktop which explains the main difference between calculated table and table created in power query.

DAX VS M which explains the main difference between M and DAX

SQL, DAX, or M which explains how to create a calendar table with three methods and their advantages.

 

Best Regards

Maggie

Veles
Advocate V
Advocate V

The advantage of 4a is it gives you one formula that you can easily replicate between reports.

 

If you have a column setup that you like for your calendar table (e.g. weekday numer, calendar week, month, year, etc.) and have that in one formula it's really easy to just copy that formula and paste it into a new report and adjust the start/end dates accordingly. If you use the New Column method then you'll have to re-do all of those steps again which could be quite onerous.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.