My ultimate goal is to create a profit and loss statement for my company from our many datasources. The problem that I am running into is that the Profit and Loss visual takes a long time to load and if you try to interact with the visual such as expand a row that takes a long time to load as well.
The Pnl is pulling from multiple data models (Orders, Revenue, Direct Materials, Expenses, Headcount) with direct query. Each of these data models has couple hundred thousand of rows so not too large. The underlying models also have multiple datasources that get merged into a single fact table. Each of these models contain individual dax queries.
The models are then combined with direct query. The individual dax queries from each model get merged into one large Dax Query with with around 100 lines that is used to create the Profit and loss statement. It includes inScope Function and Switch Functions.
I tried to solve the problem by creating summary tables for each data model so that the summary table only contains ~100 rows. I used the summarize function with dax. But I have also heard that dax queries on the summary tables are still pulling data from the original datasource so that will not speed up the process.
I am hoping to use this summary table method, but give it a small tweak. Is there a way to make the summary table a material view? Or so the summary table becomes the "data source" the queries fetch from?
