What is more efficient for a calendar table?

[aramirez7]

Hey everyone!

Quick question, what is more efficient for a calendar table?

1. Create it on the fly using DAX

2.Query a calendar table that I have in the service using DirectQuery

3.Create the table using Power Query in the current file

4. Import a Calendar table from the service

FYI:

1.The model currently uses DirectQuery - in other words, I don't need to set up a scheduled refresh. I don't want to switch to a Mixed model unless the performance change is very significant.

2. I need a very simple calendar table (Date, Year, Month, Day)

3. I need this calendar table to create a relationship with the Fact table that is in DirecQuery 

Thank you so much

Greetings from Colombia

Alejandro

[Icey · Accepted solution]

Hi @aramirez7 ,

Let's use the method of elimination.

First, you don't want to switch to a Mixed model. The defination of Mixed mode: 

---

For any Power BI Desktop file (a .pbix file) that contains some tables from DirectQuery and some import tables, the status bar displays a storage mode called Mixed.

Reference: Set the storage mode

---

Therefore, your model cannot contain imported tables. Then, methods 1, 3, and 4 do not meet the conditions and only method 2 does.

In addition, you can also consider building a date dimension table directly at the data source and then connect to it using DirectQuery mode, too.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StefanoGrimaldi]

if you already have a date dimensional table with the dates and formats you need would be better to query them in direct query mode from datasource.