Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

how to load from a source based on min/max values of another already-loaded source

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.)

1 ACCEPTED 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.

View solution in original post

7 REPLIES 7
RandyPgh
Resolver III
Resolver III

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
;

 

Anonymous
Not applicable

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.

parry2k
Super User
Super User

@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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.