Forum Discussion
Referencing vs duplicating queries speed affect
Hi
I have a fialy complex query pulling in form multiple sql tables (one for each client) then a whole bunch of formatting and cleaning tasks. This generates Table1. The table contains 3 different dates (submitted, assigned and actioned) and my model needs 3 different tables so I can relate either of these dates to my date table. So i need to create 3 subset tables of this main table with each of the subsets only needing some subset of the columns.
I see I have 2 choices
1. start each subquery with Source=Table1 then remove the columns not required
2. for each subquery use reference to Table 1
The question is in option 1 does each query get rerun in full whereas in reference only Table1 needs ot be run because then data already avaible to the other 3 i.e. is reference faster? I understand the limitations of using reference later in the model but al lmy measures wil lbe independent.
Thanks
Mike
Hi masplin
I would suggest using the second way.
As for the first way, you have to import this source four times totally even if you removed unnecessary columns. It is inefficient. If your source contains amount of data, it will take some time whether it is importing or refreshing.
5 Replies
Hi masplin
I would suggest using the second way.
As for the first way, you have to import this source four times totally even if you removed unnecessary columns. It is inefficient. If your source contains amount of data, it will take some time whether it is importing or refreshing.
I know this was marked as solved, but I think this artice provides a really good explanation and possible alternative solution if you need to manipulate the data they all have in common - Referencing Power Query queries - Power BI | Microsoft Learn
Thanks for linking the article. Was super helpful in clarifying. Forums seem to have a lot of folks giving 'expert' advice and hurting instead of helping.
I did not get it completely. But if you want to analyze data by three different dates. You can join all three with date dimension with one active and two inactive and you can use userelation to activate the relation
If you are not looking for this then, Can you share sample data and sample out. If possible please share a sample pbix file after removing sensitive information.Thanks.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Decoding Direct Query - Time Intelligence
Winner Coloring on MAP ,
HR Analytics
Power BI Working with Non-Standard TimeAnd
Comparing Data Across Date Ranges
Connect on Linkedinsorry the question is does referencing reduce the number of times the data is recovered to just once, whereas repeating the query results in multiple calls for the same data? Potentially the multi queries ot same data does not actually result in another cal las the dat is cached.
