Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Learn to Create and Manage Aggregations

  1. Home
  3. Learn to Create and Manage Aggregations

The purpose of Aggregations in Power BI is to help you reduce table sizes so that you can concentrate on important data and improve query performance. Moreover, aggregations allow interactive analysis than big data in ways that are not possible otherwise, and also can immensely reduce the cost of unlocking large datasets for decision making. We shall learn to create and manage aggregrations.

Benefits of using aggregations

  • Firstly, using aggregation lead to Better query performance over big data. Also, every interaction with Power BI visuals submits DAX queries to the dataset. Therefore, cached aggregated data utilizes a fraction of the resources required for detail data, for you can unlock big data that could not be accessed.
  • Secondly, it helps optimize the data refresh. Moreover smaller cache sizes reduce refresh times, so data gets to users quicker.
  • Lastly, it helps in building balanced architectures. Remember, the Power BI in-memory cache handles aggregated queries, thereby limiting queries sent in DirectQuery mode and assisting you to meet concurrency limits. Thereby, the remaining detail-level queries are to be filtered, transactional-level queries, which data warehouses and big-data systems generally handle well.
Aggregations in Microsoft Power BI Desktop
Source: Microsoft

Steps to create an aggregated table

Use the following steps to create an aggregated table:

  • Firstly, set up a new table with the fields you want, depending on your data source and model.
  • Secondly, define the aggregations by using the Manage aggregations dialog.
  • Then, if applicable, change the storage mode for the aggregated table.

How to manage aggregrations?

Once you have created the new table that has the required fields, then in the Fields pane of any Power BI Desktop view, right-click the table, and choose Manage aggregations.

Select Manage aggregations
Source: Microsoft

Also, the Manage aggregations dialog shows a row for each column in the table, wherein you can specify the aggregation behavior. Further, the Summarization drop-down in the Manage aggregations dialog offers the following values:

  • Count
  • GroupBy
  • Max
  • Min
  • Sum
  • Count table rows

For more visit: Use aggregation in Power BI Desktop

Microsoft Exam DA-100 Free Practice Test

Menu