I am fairly new to Power BI. I come from a very heavy Qlik Sense background.
I am attempting to source from 2 SQL Server tables. One is a fact table, of which I want all of the rows. The other is a date (dimension) table, of which I only want those dates (rows) between the min and max dates in my fact table. This way, when I create slicers and such, I don't have unnecessary dates appearing.
In Qlik Sense , the usual solution is to first load the fact table, compute and load its min/max dates into another table, set variables from this other table, load the date dimension table based on the min/max variables, and finally drop the temporary table so that it doesn't stay in the model and cause problems. This seems like the most efficient solution to me. How can I achieve this in Power BI?
I found this solution, but it seems inefficient, as it creates 2 queries (instead of just 1) for the min/max and it performs the dimension table filtering after all rows have already been fetched from the source. (In my particular case, this isn't too bad. But, it could be problematic in other situations. I would expect an efficient solution to use something like query folding / pushdown optimization.)
Solved! Go to Solution.
@qubit813 usually I have calendar creation function which take start and end date as parameter whcih can be based on Min and Max, and then a table is created from the date range passed to the function. Hope it make sense/helps.
This won't work for me. I need to load from my date table source (filtered, albeit). I cannot generate one on-the-fly in PBI, as I need lots of fiscal calendar attributes in it, which will be extremely difficult (if not impossible) to create in PBI.
@qubit813 well if you already calendar dimension coming from your data souce then the best way to filter is as per the link you posted in your original post.
If I understand that solution correctly, it does not use query-folding. So, in a situation where I'm having to filter a very large dimension table down to a small set, I would be querying the entire table first, which is incredibly inefficient. Does Power BI really not support a solution method like Qlik Sense?
The most efficient query would be one that uses the values in the SQL Server fact and date tables. Here is a template query.
WITH minmax AS (
SELECT MIN(d.date_value) AS min_date, MAX(d.date_value) AS max_date
FROM fact_table AS f
JOIN dim_date as d ON f.date_key_of_interest = d.date_key
SELECT d.* -- Not "*" here, but list all the columns that you want from your date dimension
FROM dim_date AS d
JOIN minmax ON d.date_value BETWEEN minmax.min_date AND minmax.max_date
Below is an example built from the World Wide Importers database.
WITH minmax AS (
SELECT MIN(dt.Date) AS min_date, MAX(dt.Date) AS max_date
FROM Fact.Sale as fact
JOIN Dimension.Date as dt ON fact.[Delivery Date Key] = dt.Date
SELECT d.[Calendar Month Label], d.[Calendar Year], d.Date, d.Day, d.[Day Number]
FROM Dimension.Date AS d
JOIN minmax ON d.Date BETWEEN minmax.min_date AND minmax.max_date
It turns out that the solution I referenced uses query folding after all. So, now, I just need to figure out how to collapse the 2 min/max queries down to just 1, and how to drop the tables that they create after the date dimension table loads so that they don't clutter my model.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!