Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

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.

New DAX Measure

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.

DAX Aggregation

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.

DistinctCount

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:

=TOTALYTD(SUM(FactOnlineSales[SalesQuantity]),DimDate[Datekey])

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.

TotalYTD

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.

=SUM(FactOnlineSales[SalesAmount])/CALCULATE(SUM(FactOnlineSales[SalesAmount]),ALL(FactOnlineSales))

The result can be seen below.

Ratio DAX

Recommended reading

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.

http://www.powerpivotworkshop.com/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: