Aggregation of data is a process that we’re implicitly performing every time we drag a reporting dimension in the field Values. However, when you create complex reports, it is useful to use functions for aggregation (SUM, AVERAGE, COUNT) to create measures that will be used in the report. Once thought out measures may be used as a building block to create complex DAX expressions.
In one of previous recipes we were talking about basic concepts of DAX, where you’ve got acquainted with calculated columns. Now let’s say something about how to manipulate them within the table that exist in the Data model. First, we will show how to filter them, then how to remove filters and how to perform sorting. Then we will be talking about adding a new column, “freezing” and “unfreezing” columns, changing the column width, renaming…
DAX (Data Analysis Expressions) is a language used to create expressions (formulas) for making the reporting dimensions that are used in Power Pivot tables. Once created these expressions can be both used within tabular model of Microsoft SQL Server Analysis Services. A large number of DAX functions have the same syntax as Excel functions, while others can work with relational data and perform dynamic aggregation of data, their filtering etc.
Power Pivot has functionality that offers the choice of a data set that will be used to create reports. Under a set of arbitrarily selected data I mean that for some reason we want to follow, and we want to save them and, if necessary, all together add to the table. Once created sets can be seen as all other reporting dimensions, and their inclusion or exclusion of we influence on the contest of display data.
When we use Power Pivot reports often happens that we’ll use two or more reporting dimensions, hierarchically arranged, to provide organized access to information. If something like this occurs frequently we can use specific functionality of Power Pivot reports: Hierarchy. Hierarchy is a named set of hierarchically arranged reporting dimensions, and more about ways for creating and using them you’ll find out if you read this recipe.