Kasper de Jonge PowerPivot Blog | Bringing BI to the masses DAX Statistical Functions Following on from his first four articles on using Data Analysis Expressions (DAX) with tabular databases, Robert Sheldon dives into some of the DAX statistical functions available, demonstrating which are the most useful and examples of how they work. The Data Analysis Expressions (DAX) language includes a wide range of functions that help you refine your queries when retrieving data from a SQL Server Analysis Services (SSAS) tabular database. You’ve seen numerous examples of DAX functions throughout this series on the tabular model. One set of functions that are particularly useful when working with tabular data are the statistical functions, which support various ways to aggregate and analyze data. In this article, the fifth in our series, we explore many of the DAX statistical functions and provide numerous examples that demonstrate how they work. To follow along in this article, you should have a basic understanding of how to write a DAX query. The Row Function The Topn Function
Microsoft OLAP Blog by Hilmar Buchta: Semi additive measures in DAX / BISM Tabular SQL Server Denali | PowerPivot Semi additive measures, i.e. measures that have to be aggregated differently over different dimensions, are commonly used in BI solutions. One example could be stock levels. Of course we don’t want to sum them up over time, but only over product, location etc. For the time, a different aggregation is used, for example average or last value. The following example shows how to implement some of the most commonly used semi additive measures in DAX. In my example I’m using PowerPivot (Denali edition), but the same calculations can be used in a BISM Tabular model in Visual Studio. In order to keep things simple, I’m using just a short table of test data: As you see, we only have two products with monthly stock levels in 2010 and 2011. Although not needed for my semi additive measures, I created additional columns in my PowerPivot sheet for convenient reasons: Year, Month, Day (using the corresponding DAX-function with the same name). Average (over time)
DAX (Data Analysis Expressions) Measures in PowerPivot - Analysis Services and PowerPivot Team Blog Introduction to DAX Measures It’s been a while since I talked about the DAX formula language in PowerPivot, and wanted to take this opportunity to present some examples showing how DAX formulas can be used to define measures in PowerPivot. Before we get to the new functions and examples, let me review some basic points about the DAX expression language: DAX formulas can be used to define calculated columns in a table and they can also be used to define measures. Calculated columns are just like in Excel – you enter a formula, and that formula is evaluated for each row in the table, effectively filling in the entire column with values. A measure is different. This PivotTable has only one measure, named “Sales” with a single DAX formula defined as =SUM(FactSales[SalesAmount]) That single formula is being evaluated 72 distinct times, with 72 different results. The dialog in which the measure is defined has multiple entry points: Sample Data comes from the Contoso database Filter Context