I am trying to limit the amount of editing I have to do when I update reports to include new periods. There are no date fields in the data sources where I am pulling, so the periods are chosen as a list. Currently, for every report dataset, I must go into every single query in a report and choose the same terms over and over again.
What I would like to do is create one new query where I can choose the periods to include one time, and inner join that query to all other queries in my file so I can edit one query and on refresh all queries will update to my new chosen items. The purpose of the inner join is to limit the rows on multiple queries to a list of options where I only have to choose the list items one time when I update and refresh a dataset.
It is essentially using the one query as a parameter for all other queries; I can't use an actual parameter because as far as I know parameters only support choosing one item at a time.
The problem is that this loads EXTREMELY slowly. I notice that when it is loading the total number of rows after merging should be around 600,000, but the query seems to be pulling every single row in that datasource and THEN performing the merge, because after about an hour or so the query stops and I end up with the right number of rows.
I posted in a similar thread about poor performance with inner joins when pulling data from Access but I guess that thread was about getting data from Access more than the issue with inner join performance.
Edit: I wanted to add that when I connect to the cube in Power Pivot and use the query designer, and I add a dimension to the filter area, all dimension categories are immediately available when I click the drop down. Whereas in Power BI it seems to query the entire fact table to find unique dimension categories. Why is Power Pivot able to interact with the same cube correctly while Power BI seems to need to scan the whole fact table every time I click anything?
