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
JoeMac
Regular Visitor

Material View for Summary Table

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?  

2 REPLIES 2
v-jingzhan-msft
Community Support
Community Support

Hi @JoeMac 

 

To my knowledge, if you create a calculated table as a summary table with DAX and use data from this calculate table to populate visuals, the DAX queries from these visuals will be sent to the calculated table instead of the data sources, which should make interaction faster. However, a calculated table is in Import mode not DirectQuery, so it will not always be updated automatically to have real-time data from the data sources. You need to execute refresh manually or set up a scheduled refresh in Power BI Service to update the data in the summary calculated table. 

 

Best Regards,
Jing

lbendlin
Super User
Super User

Sort of  - All the secrets of SUMMARIZE - SQLBI

 

The recommendation is to use SUMMARIZECOLUMNS if possible.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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
Top Kudoed Authors