Mastering DAX Calculations
Create advanced analytical models by learning how to harness the power of DAX calculations for use within Power BI. DAX is the most difficult part of Power BI to learn and put into practice well. This course will take you from a beginner to an advanced user of Power BI by utilizing the powerful DAX language to the fullest.
Here's What You'll Get
With The Course
More than 9 hours of course videos
1x demo data sets to follow along when practicing all DAX formula
Discover all major tips and techniques by Mastering DAX Calculations in Power BI
- Through real-world scenarios, you will be taken through how to use DAX in practice to discover the insights you want to find in your data.
- Covered are every major DAX function and how to use these when completing calculations and in combination with each other
Details Of What You Will Learn
During This Course
- Learn - what DAX is and the journey required to master it
- Understand - what you can achieve with DAX if learnt well
- Review - DAX basics, including calculated columns vs measures, context & aggregation vs iteration formulas
- Understand - why a quality data model is so important
- Complete - a deep dive into simple aggregation and iterating functions
- Review - the concept of 'context' comprehensively
- Learn - the most important function in DAX, CALCULATE
- Learn - all the major table and filtering functions
- Review - how to use DAX in common analytical scenarios
- Gain - a detailed overview of time intelligence functions and how to use within your models
- Learn - advanced table functions and how to use them
- Master - some of the most complex topics around how DAX actually works
This Course Module is Tailored for:
Data Analysts
Reporting Managers
Excel Users
Excel users, data analysts, reporting managers
LEARN HOW TO
Utilize the key features within the
Mastering DAX Calculations
- Use DAX and also what you can achieve with DAX if utilized well
- Structure and format DAX formula
- Use DAX variables in your formulas effectively
- Use calculated columns and measures in your models
- Write aggregation, iteration and table DAX functions
- Implement DAX functions in combination with each other
- Visualize how the DAX calculation engine works via the concept of 'context'
- Audit your formulas by understand filter and row context
- Use the CALCULATE
- Add additional virtual filters to your calculations by using table functionsalysis
- Use DAX to solve common analytical scenarios you will face everyday in the real world
- Use time intelligence table along with a quality date table
- Use advanced table functions in unique calculation requirements
Course Curriculum
1. Introduction to DAX
- How to get certified for this course?
- Course resource pack download
- What is DAX & how to learn to master it?
- Data types and operators
- The importance of naming conventions
- Formula syntax
- Measure groups
- Formatting DAX code
- Using variables to simplify formulas
- Writing comments in your code
- Quick measure feature (positives vs negatives)
- DAX doesn't work unless your data model and relationships are setup correctly
- The only way to learn is to practice
2. DAX Basics
- Calculated columns vs measures
- Understanding context & how the DAX calculation engine works
- Evaluation or initial context
- Aggregations or filter context
- Iterations or row context
- Understand context in calculated columns (context transition)
- How the data models iterates w/DAX (filter propagation)
3. Getting started with DAX
- DAX function types
- Aggregation Functions - SUM/AVERAGE/MIN/MAX
- More Aggregators - COUNT/COUNTA/COUNTBLANK/COUNTROWS/DISTINCTCOUNT
- Iteration Functions - SUMX/AVERAGEX/MINX/MAXX
- Measure branching methodology
- How to use combinations of DAX functions
- Error handling w/DAX - BLANK/ISBLANK/IFERROR
- Logical Functions - IF / SWITCH
- DIVIDE function
- Other Function Types - Information, Text, Conversion, Date & Time, Boolean
4. Introducing Simple Table Functions
- What are table functions? (think of virtual tables!)
- FILTER
- The versatile VALUES function
- VALUES vs DISTINCT
- VALUES vs SELECTEDVALUE (& HASONEVALUE)
- ALL function - the remove filters function
- ALL & ALLEXCEPT
- ALLSELECTED requires a deeper understanding
5. Expanding on CONTEXT
- How filter and row context work with relationships
- Working with one table versus many tables
- ISFILTERED/ISCROSSFILTERED
- RELATED function
- EARLIER function
6. CALCULATE Statement
- Why CALCULATE is important?
- Why do you even have to use CALCULATE? (It's all about CONTEXT!)
- What can you do when you use CALCULATE?
- How are formula calculated when you involve CALCULATE?
- CALCULATE example w/USERELATIONSHIP
- Simple and advanced filters with CALCULATE using FILTER function
7. Time Intelligence Functions
- What are time intelligence functions?
- Why you must have a date table?
- The best time comparison function - DATEADD
- DATEADD vs SAMEPERIODLASTYEAR
- Aggregations - YTD, QTD, MTD
- Time comparisons - PREVIOUSMONTH, PARALLELPERIOD
- Information - OPENINGBALANCEMONTH, STARTOFMONTH, ENDOFYEAR
- DATESBETWEEN, DATESINPERIOD, DATEDIFF functions
- Practical examples using time intelligence functions
- Download your PBIX here
- Basic Labs Assessment
- Advanced Labs Assessment
8. Advanced Techniques For Variables
- Understanding how variables are evaluated
- Using table variables to simplify ultimate formulas
- Using variables to clean up multiple measures
9. Creating Virtual Relationships
- When to create virtual vs physical relationships?
- TREATAS
- How does USERELATIONSHIP compare?
10. Advanced Table Functions
- How to manipulate virtual tables using table functions
- ADDCOLUMNS
- SUMMARIZE
- You can also combine table functions together
- ROW
- UNION
- CALCULATETABLE
- INTERSECT & EXCEPT
- SUMMARIZECOLUMNS
- GENERATE
- GROUPBY
11. Ranking Functions
- RANKX
- TOPN
- Practical examples for using ranking functions
12. Advanced DAX Formula Combination Examples
- This is where the measure branching methodology really shines
- Compare multiple quarterly sales on each other
- Using virtual tables with COUNTROWS
- Budgeting allocation algorithm
- Fixing errors with your totals efficiently
Your Instructor
Sam McKay
Sam is the founder and CEO at Enterprise DNA, a business intelligence training & consultancy business primarily focused on driving better decision making within organizations by empowering individuals and teams to transform raw data into unmatched insights using Microsoft's Power BI suite of tools.
Sam has a strong history in Finance, previously working at ANZ, PIMCO, and Fonterra. He is also a CFA charterholder, achieving the designation in 2011.
He has a passion for building highly scalable analytical solutions within organizations and wants to see others learn how to do the same.
Frequently Asked Questions
- How long do I have access to the course? You have access to this course as long as you have Enterprise DNA Membership Subscription.
- Can I purchase a single course instead of the full Enterprise DNA membership? The option to purchase a single course has been discontinued. To access any of our courses, you will need to upgrade to full membership here.
- Do you offer one-off support or coaching? All support around Power BI and Enterprise DNA's online training content now occurs at the Enterprise DNA Forum. You must have an Enterprise DNA Membership or CoE access to receive support.
- What if I need to train my team? We recommend exploring Enterprise DNA Center of Excellence platform. To learn more, see here.
Get full access to unparalleled training
& skill-building resourcess
For Individuals
Enterprise DNA
Membership
Empowering the most valuable Power BI users to expand their analytical thinking and insight generation possibilities
For Teams
Enterprise DNA
Center of Excellence