Learn to write DAX a practical guide to learning Power Pivot for Excel and Power BI

1: Concept: Pivot Tables vs. Power Pivot...............................................................................................3

2: Concept: Loading Data.....................................................................................................................5

3: Concept: Calculated Fields..............................................................................................................24

4: DAX Topic: SUM(), COUNT(), COUNTROWS(),

MIN(), MAX(), COUNTBLANK(),

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 HASONEVALUE()..........................................................................................

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