cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

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

Accepted Solutions
Highlighted
Super User IV
Super User IV

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

@qubit813 you can turn off Data loading the model, just right click on the query, and uncheck Enable Load.






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
Highlighted
Super User IV
Super User IV

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

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






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.





Highlighted
Helper IV
Helper IV

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

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.

Highlighted
Super User IV
Super User IV

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

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






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.





Highlighted
Helper IV
Helper IV

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

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?

Highlighted
Resolver II
Resolver II

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

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
;

 

Highlighted
Helper IV
Helper IV

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

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.

Highlighted
Super User IV
Super User IV

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

@qubit813 you can turn off Data loading the model, just right click on the query, and uncheck Enable Load.






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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors
Top Kudoed Authors