Inner Join in Power BI created by Power BI Desktop based on Date vs Year?

[Anonymous]

Hi,

I have a fact table (30m rows) + a calendar table (dim_time).

The dim_time table is created in the datawarehouse and has a range of dates from 1980 to 2050.

I am doing a Inner Join on the date field to just get necessary rows in the calendar table.

Does anyone have any recommendations on how to solve this? Is a Inner Join the best operation for this? Or is better to maybe fech the min / max date from the fact table and use that to filter out the dim_time table instead of doing an inner join?

I've also tought about creating a year column in the fact table and instead of using Date as the Inner Join field use Year in the fact table + Year in the date table and am wondering if that would go faster?

Thanks for any suggestions.

Best,

Ali A

[Anonymous]

Hi,

I would take the second suggestion of yours to be better. You could Create a Measure with two variables which would take the First and the Last Dates in the Fact Table , and be using these Variables to Filter and return the Table. I assume you would be aware of this syntax, but just to make sure that  i put my Idea across lucently.

VAR First_Date= FIRSTDATE(Fact[Date])
VAR Last_Date=LASTDATE(Fact[Date])
RETURN CALCULATETABLE(Date,FILTER(Date, Date[Date] >= First_Date && Date[Date] <= Last_Date))

[Anonymous · Accepted solution]

Hi again,

Thanks for your reponse.

What I did instead is add this to the dim_time (calendar table):

Add a step to filter rows and then I used the following logic to filter it based on the lowest and highest date value I found in the fact table.

= Table.SelectRows(columns_renamed, each [Year] >= Date.Year(List.Min(#"fact_distribution"[Date])) and [Year] <= Date.Year(List.Max(#"fact_distribution"[Date])))

Worked much better and I had to avoid the inner joins which took a lot of time.

Here is the syntax for finding the min and max year (based on date) in Power Query (if anyone else wants it):

Min: = Date.Year(List.Max(#"TableName"[DateField]))

Max: = Date.Year(List.Min(#"TableName "[DateField]))

Best,

Ali A