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.
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.