iii Table of Contents 1: Concept: Pivot Tables vs. Power Pivot .3 2: Concept: Loading Data.5 3: Concept: Calculated Fields .24 4: DAX Topic: SUM(), COUNT(), C OUNTROWS(), MIN(), MAX(), C OUNTBLANK(), and DIVIDE() .34 5: Concept: Filter Propagation .51 6: DAX Topic: The Basic Iterators SUMX() and AVERAGEX() .57 7: DAX Topic: Calculated Columns.66 8: DAX Topic: CALCULATE() .69 9: Concept: Evaluation Context and Context Transition .76 10: DAX Topic: IF(), SWITCH(), and FIND() .80 11: DAX Topic: VALUES() and H ASONEVALUE() .83 12: DAX Topic: ALL(), ALLEXCEPT(), and ALLSELECTED() .90 13: DAX Topic: FILTER() .103 14: DAX Topic: Time Intelligence .112 15: DAX Topic: RELATED() and RELATEDTABLE() .135 16: Concept: Disconnected Tables .139 17: Concept: KPIs and Multiple Data Tables .149 18: Concept: Cube Formulas .160 19: Moving from Excel to Power BI .166 20: Next Steps on Your DAX Journey .173 Appendix A: Answers to Practice Exercises .175 Index .182.
iv Learn to Write DAX Acknowledgements There are a few people I want to thank who have encouraged and helped me in my venture into my own business and helped me to get this book written and released: Rob Collie: What can I say but thanks! Thanks for trailblazing this Power Pivot thing and making my journey so much easier. And thanks for everything you have personally done to help me succeed.
v Introduction Power Pivot is a revolutionary piece of software that has been around since 2009. Despite it being more than six years old at this writing, most people who could benefit from Power Pivot still don't know it exists. The good news is that you are not one of those people. If you are reading this, then you already know about Power Pivot, and chances are good that you already know enough about it to know that you need to learn to write DAX.
1: Concept: Pivot Tables vs. Power Pivot 3 1: Concept: Pivot Tables vs. Power Pivot Some people wonder what the difference is between Power Pivot and pivot tables, so I'm going to start by explaining. Read on, and you'll have it sorted out in no time.
2: Concept: Loading Data 5 2: Concept: Loading Data Before you can start to write DAX and use Power Pivot, you need to load some data. Power Pivot always loads a complete copy of the source data into the Data Model as the first step in the process. Once it's loaded, you can share your workbooks with others, and there is no need for anyone else to have direct access to your source data.
24 Learn to Write DAX 3: Concept: Calculated Fields Calculated fields have been around for many years—maybe as long as pivot tables themselves. But the original calculated fields are not the same thing as the newer Power Pivot calculated fields in Excel 2013.
34 Learn to Write DAX 4: DAX Topic: SUM(), COUNT(), COUNTROWS(), MIN(), MAX(), COUNTBLANK(), and DIVIDE() This chapter starts out with some basic DAX formulas to get you started. Most of the DAX functions in this chapter accept a column as the only parameter, like this: =FORMULA(ColumnName). The only exception is =COUNTROWS(Table), which takes a table (not a column) as the parameter.