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.
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.
@Anonymous you can turn off Data loading the model, just right click on the query, and uncheck Enable Load.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@Anonymous you can turn off Data loading the model, just right click on the query, and uncheck Enable Load.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@Anonymous 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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?
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |