Creating a Power BI Solution using Dataflows for Aggregations

Power BI Aggregations provide a way to query a data source that is too large to fit within a Power BI dataset by allowing an imported aggregates table to satisy certain calculations whilst other calculations can fall back to using a live (DirectQuery) connection to the underlying data source. This is known as a Composite Model. In this scenario-based blog post we’ll create a Power BI solution which analyses sales data where the source data is considered too large to fit within a Power BI dataset. E.G. if the underlying data source Fact table at the Order Date and Customer granularity exceeded limits for a Power BI Desktop model, the model can be setup to use the Order Date as an aggregate and the Customer as a live connection. When a report viewer uses Order Date and an appropriate calculation in a report, this can be satisifed with the imported cache, whilst using Customer will result in a connection to the underlying data source.

Chapters:
00:00 Introduction
00:50 Create Dataflow
03:04 Visual Data Prep in Dataflow to transform data
06:43 Use GroupBy to create aggregate transformation
09:00 Create data model in Power BI Desktop
15:50 Create new table using DirectQuery
21:35 Create aggregations in data model
23:01 Create measures table with new DAX measures
25:36 Create data visualisations and test aggregations

[MilhouseBS]

Very nice demo of Power BI and Power Query in general also! Thank you.