Useful DAX Starter Functions and Expressions
Posted by thomasivarssonmalmo on April 25, 2011
I have been a little slow with the expression language of PowerPivot DAX mainly because I felt it a little hard to understand. Maybe this problem comes from my mind being occupied by MDX, TSQL and the expression language in Reporting Services.
Right now I have som starter functions that can help with getting to know the DAX syntax better and perhaps answer some common business questions that are not to complicated. All my examples comes from the Adventure Works star schema model that you need to import into PowerPivot first.
Start with doing your calculations on top of a Pivot Table with PowerPivot tables as the source. Calculations in the PowerPivot tables will only make your more confused. You find this editor under the PowerPivot tab in the Excel ribbon under new measure. Make sure that it is the fact table that is selected as the table name at the top.
DAX Aggregation formulas
Here I am thinking about the aggregation formulas that you get when you drop a measure in the values box in the Pivot Table design tool. You do not need the editor above to create these standard aggregation formulas.
The formula can be seen above the OK button in the picture above. If you drop a measure you get: SUM(FactOnlineSales[SalesAmount]). Wrapped in the SUM function is the table name combined with the column name. If you drop a text column in the values box you will get the COUNTA function instead which makes sense since you cannot SUM text values. This is the COUNTA example: COUNTA(FactOnlineSales[SalesOrderNumber]). If you try to change this to COUNT you will get this error message: “The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String.”
The beauty of DAX: Distinct Count
Here comes a new taste of DAX where we need to combine to DAX functions to get the result we want. Distinct Count is hard to set up in Analysis Services beacause of dfferent technical requirement of that model. In PowerPivot and DAX we have this available by using two functions that are not too complicated. Remember that end users often confuse Count and Distinct Count.
The syntax is as this: =COUNTROWS(DISTINCT(FactInternetSales[CustomerKey])).
First it is the CountRows function and with the Distinct function inside of it. In the Distinct function we point to the dimension key that we are interested of doing a distinct count on. In this case it is the CustomerKey in the fact table. ProductKey or any other foreign key will work in the same way.
DAX Time Calculations
It is important that your PowerPivot model has complete years regarding dates in order to avoid strange results. The Adventure Works star schema model has this limitation.
First is the accumulated value of a measure within a year frequently called YTD(Year to Date). Here we combine the outer TotalYTD function with the SUM function like this:
The example is from the Contoso Sales sample database. DimDate is the date dimension table and the DateKey is a date column. The fact table name here is FactOnlineSales and SalesQuantity is the measure in that fact table.
The result looks like this in Excel 2010.
Ratios in DAX
Ratios introduce a new generic DAX function called Calculate and a little different thinking than in the previous examples. If we want to build a ratio in DAX with the measure SalesAmount we need the nominator, that is the current aggregated value in the Pivot Table and divide that with the denominator or total of SalesAmount that is always the same.
In DAX and with the Contoso sales example database this can be built in this way.
The result can be seen below.
There is one book that I can strongly recommend if you want to learn more about DAX and PowerPivot models and that is Marco Russo’s and Alberto Ferrari´s “PowerPivot for Excel 2010 Give Your Data Meaning. More information can be found on this link.