In one of the earlier recipes was discussed about Data model, tables that we’re adding to it and relationships between them. This relationships are not very useful when when we’re writing DAX expressions. If we wan to use, as a formula argument, column from related column you should somehow emphasize it. This is done by using functions RELATED and RELATEDTABLE, and about which you can read more in the following text …
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…
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.
Power Pivot is an interactive tabular report, similar to traditional Pivot table, from which it differs in that it is based on tables that belong to the Data model. It can handle large amount of data that are unpacked when needed, when we have to use them in report, thus saving memory space. It has several specific options, such as KPI, hierarchies or data sets, which can be very helpful when we are making complex reports.