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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vikkidurai91
Helper I
Helper I

Using Source of one query for another

Hello

I have a .pbix file, where we import few views and tables(Oracle). In some queries, the view imported are same but steps are different.

Example : 4 queries imports same view and then has different steps. 

 

Is there any possibility to import that view in one query, and this imported view can be used as a source for other queries within pbix file? This will help me in avoiding unnecessary 4 imports of same view and have one import of view and use the same as source for other queries.

 

please suggest.

 

Thanks

Vignesh

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Yes, absolutely.

  1. Do your initial import of the view and any transformations you want. You don't have to do any, but you might want everything to be filtered for the last 2 years for example. Do that here so you don't have do that 4 times in the next step.
  2. Right-click  on that query and select REFERENCE. That will make query 1 the source of query 2. Any changes to 1 will affect 2 (and 3, 4, 5.)

Do not select DUPLICATE. That will copy the first query and changes made in 1 will no longer affect 2. You might want that for other scenarios, but it doesn't sound like you do for this question.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

9 REPLIES 9
amitchandak
Super User
Super User

In the ci=onnection you have an advance option where you can write down query.

AdvanceProperty.png

edhans
Super User
Super User

Yes, absolutely.

  1. Do your initial import of the view and any transformations you want. You don't have to do any, but you might want everything to be filtered for the last 2 years for example. Do that here so you don't have do that 4 times in the next step.
  2. Right-click  on that query and select REFERENCE. That will make query 1 the source of query 2. Any changes to 1 will affect 2 (and 3, 4, 5.)

Do not select DUPLICATE. That will copy the first query and changes made in 1 will no longer affect 2. You might want that for other scenarios, but it doesn't sound like you do for this question.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi @edhans 

 

Thanks for your response. yes I tried with Reference already. When I refresh the refrenced query, there is always 2 sessions coming to DB. One from Actual query and other from referenced query. So I was confused, if it's being effective. Also my .pbix file is 1.8Gb and with this reference(common View Queries), my .pbix grew to 2.4 GB. Thats again a problem for me.

It is effective.

A few things:

  • Make sure your first query is set to NOT LOAD. Right-click and tell it not to load. That will solve the size issue. You only want the tables you need in the DAX model to load. the others will still be used in calculations, but will not load their results to DAX, merely hand them off to referenced queries.
  • It will make multiple requests to the DB. Power Query works bottom up, not top down. So if you have 3 queries referencing 1, and 1 is the databse, it will do the 3 end queries and go to the top, hitting your DB 3 times.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

HI @edhans  , @amitchandak 

 

I adapted my report again with Reference and disabled the load.

 

I see the performance has increased, but I can check on Monday morning. I will keep you posted.

 

Thanks a lot for your help!

Hi @vikkidurai91 ,

 

as far as I know, you should deactivate parallel loading in the options so that the queries uses the cache.

 

There are good contributions from Chris Webb.

https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


@edhans  Thanks for your suggestion. We tested for last 2 days and Reference feature has solved the issue.

 

Thanks all for the help.

Excellent @vikkidurai91 . Glad my suggestion helped and it is helping your project move forward!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks @edhans . I will check this and will let you know. Referencing is a very good option.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors