Revision #70

Welcome to the DAX Resource Center Wiki - your one stop shop for all things DAX

This Wiki includes articles, whitepapers, videos, and samples published by both Microsoft as well as experts in the Business Intelligence community. Because this is a Wiki, you too can contribute. If you have some great information about DAX and how you use it in your organization, please share it!

You can contribute!

The best information comes from those who use DAX. When starting out, you can use this Wiki to help you learn how to use DAX. As your knowledge about DAX grows, you may find creative and unique ways how to use DAX to solve real-world business problems in your organization. Others can benefit from your skills. You can post anything you want about DAX. See the right side of this page to learn more about how you can contribute.

Introduction to DAX

Data Analysis Expressions (DAX), is a formula expression language used to define calculations in PowerPivot for Excel® workbooks and Tabular model projects authored in SQL Server Data Tools. DAX functions provide extensive filtering to calculate on data across multiple tables, work with relationships ,and perform dynamic aggregation.

Whether you are new to writing formulas or are well experienced using Excel formulas or MDX, these downloads, articles, and videos will help you get started with understanding DAX:

Articles

• QuickStart: Learn DAX Basics in 30 Minutes - This QuickStart is for users new to PowerPivot or Analysis Services Tabular model projects authored in SQL Server Data Tools. It is meant to give you a quick and easy introduction on how you can use Data Analysis Expressions (DAX) to solve a number of basic data modeling and analytical problems. This topic includes conceptual information, a series of tasks you can complete, and a few quizzes to test what you’ve learned. After completing this topic, you should have a good understanding of the most basic fundamental concepts in DAX.

Downloads

• DAX in the BI Tabular Model Whitepaper and Sample Workbook – This downloadable package from Microsoft Download Center includes the DAX in the BI Tabular Model Whitepaper, with extensive information about DAX in both PowerPivot for Excel and SQL Server 2012 Analysis Services Tabular model projects.  This package also includes the Contoso DAX Sample Formulas.xlsx PowerPivot workbook, which includes the formulas described in the whitepaper as well as many other formulas used in both calculated columns and measures.

Videos

• DAX Patterns - Marco Russo

Microsoft DAX Documentation

Microsoft official documentation for DAX is provided on Office.com, MSDN, as well as in PowerPivot for Excel Help, installed with PowerPivot for Excel 2010.

• If you are using PowerPivot in Excel 2013, you can find a lot of great DAX information, including the DAX Reference, at Calculations in PowerPivot in Excel 2013 on Office.com.
• The Data Analysis Expressions (DAX) Reference on MSDN provides the most detailed and up-to-date DAX information resource available. Included in the DAX Reference are Syntax Specification, Operator Reference, and the DAX Function Reference, which includes detailed information about each DAX function, many of which include helpful formula examples.
• If you are using DAX formulas in Tabular modeling projects authored in SQL Server Data Tools (Microsoft Visual Studio 2010 or later with the SSDT add-in), documentation is provided on MSDN at Analysis Services Tabular Modeling. Also, the Tabular Modeling (Adventure Works Tutorial) includes many lessons on creating DAX formulas for calculated columns, measures, and row level security using roles.
• When you install PowerPivot for Excel 2010, the Help file includes the Create Your First PowerPivot Workbook tutorial, which includes several lessons which guide you through creating DAX formulas for calculated columns and measures. Also, in the Analyze Data section, you can learn how to add calculations by using DAX. Included here are many topics about DAX as well as the DAX Reference.

 

Most Valuable Resources

You’ve heard about MVPs (Most Valuable Professionals), but what about MVRs (Most Valuable Resources). Okay, not so esteemed as MVPs, but super important nonetheless. And hey, many of the MVRs are written by MVPs. Here are links to some of the most valuable resources you can use to find out more about DAX

If you also provide an MVR about DAX, be sure to post a link to it here!

Websites and Blogs

• SQLBI - Marco Russo
• Alberto Ferrari
• Boyan Penev on Microsoft BI
• Cathy Dumas
• Kasper de Jonge’s BI Blog
• PowerPivot Team Blog
• =powerpivotpro.com
• PowerPivot-Info.com
• PowerPivot Geek
• Paul te Braak
• Prologika (Teo Lachev's Weblog)
• Chris Webb's BI Blog 

Books
There are many great books written by leading BI professionals to help you learn about DAX, particularly in PowerPivot workbooks. Here are just a few available at leading book retailers.

• Applied Microsoft SQL Server 2012 Analysis Services: Tabular Modeling - By Teo Lachev.  New!
• Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model - By Marco Russo, Alberto Ferrari, Chris Webb. New!
• Microsoft PowerPivot for Excel 2010: Give Your Data Meaning - By Marco Russo and Alberto Ferrari.
• PowerPivot for the Data Analyst: Microsoft Excel 2010 - by Bill Jelen.
• Professional Microsoft PowerPivot for Excel and SharePoint (Wrox Programmer to Programmer) - by Sivakumar Harinath, Ron Pihlgren and Denny Guang-Yeu Lee.Practical
• PowerPivot & DAX Formulas for Excel 2010 – by Art Tennick.

 

Samples

These resources will help you understand DAX formulas by providing formula examples used to solve real-world business problems:

Articles

• DAX Cheat Sheet – Vidas Matelis
• DAX: I’ve Been Shortchanging You – Rob Collie
• DAX: Running Totals – Chris Webb

Downloads

•  DAX in the BI Tabular Model Whitepaper and Sample Workbook – MSFT
•  PowerPivot DAX Samples - MSFT

Videos

• PowerPivot DAX Samples from the Microsoft Download Center

DAX formulas in measures

Measures (also knowsn as calculated fields in PowerPivot in Excel 2013), in workbooks and tabular projects are essential for getting the most out of your data. These resources by MVPs and users just like you can help you understand DAX formulas used in measures:

Articles

• DAX Measures in PowerPivot - Howie Dickerman
• Introducing PowerPivot DAX Measures - Rob Collie
• Change How PowerPivot Handles DAX Measures - Vidas Matelis
• Derived Measures - Rob Collie
• Build a Cash Flow Statement Using Dynamic Measures - Kasper de Jonge
• Use Slicer Values In a Calculation - Kasper de Jonge
• Create a Calculated Measure that Converts a Numeric Result to a String Format - Vidas Matelis

Videos

• DAX "Qty per Day" Measure 1 - Rob Collie
• DAX "Qty per Day" Measure 2 - Rob Collie

DAX functions

Using functions in DAX formulas is essential for creating the most effective calculations. These resources from MVPs and users can help you in understanding the different types of functions in DAX and how you can use them:

Articles

• DAX Function Reference on TechNet - MSFT
• List of PowerPivot DAX Functions - Vidas Matelis ; and With Descriptions
• Time Intelligence Functions in DAX - Howie Dickerman
• Time Intelligence Without Date Columns - Chris Webb
• ALL() Function - Rob Collie
• Why Use ALL() and How to Work Around It - Kasper de Jonge
• CALCULATE() Function: Pivots Will Never Be the Same - Rob Collie
• CALCULATE is a Supercharged SUMIF - Rob Collie
• CALCULATE and Rapid Iteration - Rob Collie
• CLOSINGBALANCE Functions - Vidas Matelis
• Touchdown and COUNTROWS - Rob Collie
• COUNTROWS: Counting Returning Customers - Chris Webb
• Don't Overuse the FILTER() Function - Rob Collie
• =RELATED() Function - Rob Collie
• SUMX(): The 5-Point, Palm Exploding FXN Technique - Rob Collie
• Year-to-date: TOTALYTD - Chris Webb
• Solving the "Events in Progress" Problem - Chris Webb
• "Events in Progress" Problem: Aggregating Dates and Multiselect - Chris Webb
• "Function Only Works with Contiguous Date Selections" - Chris Webb :

Videos

• Introduction to Time Intelligence Functions - Kasper de Jonge
• DAX Time Calculations - Element61

Dax Studio

Credits

This article was originally written by Owen Duncan (Microsoft SQL Server Technical Writer) and Ed Price.