cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aliahmad Regular Visitor
Regular Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
aliahmad Regular Visitor
Regular Visitor

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

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

2 REPLIES 2
Mvignesh53 Member
Member

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

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))
aliahmad Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 285 members 3,030 guests
Please welcome our newest community members: