Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |