English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 9.5 Hours | 4.82 GB
Create advanced analytical models by learning how to harness the power of DAX calculations for use within Power BI
This training will take you from a beginner to advanced user of Power BI by utilizing the powerful DAX language to the fullest. Covered in the course material are all the beginner theory that you must understand very well to harness the analytical power of DAX. 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. DAX is the most difficult part of Power BI to learn and put into practice well. This course covers all major tips and techniques that if practiced will have any Power BI user creating compelling Power BI reports and solutions.
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
What you get with the course
- 11 hours of training
- 1x demo data sets to follow along when practicing all DAX formula
Here are the specifics for the course
Learn how to:
- Use DAX and also what you can achieve with DAX if utilised 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
- Visualise how the DAX calculation engine works via the concept of ‘context’
- Audit your formulas by understand filter and row context
- Use the CALCULATE function in your analysis
- Add additional virtual filters to your calculations by using table functions
- 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
Table of Contents
Introduction to DAX
1 What is DAX & how to learn to master it
2 Data types and operators
3 The importance of naming conventions
4 Formula syntax
5 Measure groups
6 Formatting DAX code
7 Using variables to simplify formulas
8 Writing comments in your code
9 Quick measure feature (postives vs negatives)
10 DAX doesn’t work unless your data model and relationships are setup correctly
11 The only way to learn is to practice
DAX Basics
12 Calculated columns vs measures
13 Understanding context & how the DAX calculation engine works
14 Evaluation or initial context
15 Aggregations or filter context
16 Iterations or row context
17 Understand context in calculated columns (context transition)
18 How the data models interates w DAX (filter propagation)
Getting started with DAX
19 DAX function types
20 Aggregation Functions – SUM AVERAGE MIN MAX
21 More Aggregators – COUNT COUNTA COUNTBLANK COUNTROWS DISTINCTCOUNT
22 Iteration Functions – SUMX AVERAGEX MINX MAXX
23 Measure branching methodology
24 How to use combinations of DAX functions
25 Error handling w DAX – BLANK ISBLANK IFERROR
26 Logical Functions – IF SWITCH
27 DIVIDE function
28 Other Function Types – Information, Text, Conversion, Date & Time, Boolean
Introducing Simple Table Functions
29 What are table functions (think of virtual tables!)
30 FILTER
31 The versatile VALUES function
32 VALUES vs DISTINCT
33 VALUES vs SELECTEDVALUE (& HASONEVALUE)
34 ALL function – the remove filters function
35 ALL & ALLEXCEPT
36 ALLSELECTED requires a deeper understanding
Expanding on CONTEXT
37 How filter and row context work with relationships
38 Working with one table versus many tables
39 ISFILTERED ISCROSSFILTERED
40 RELATED function
41 EARLIER function
CALCULATE statement
42 Why CALCULATE is important
43 Why do you even have to use CALCULATE (It’s all about CONTEXT!)
44 What can you do when you use CALCULATE
45 How are formula calculated when you involve CALCULATE
46 CALCULATE example w USERELATIONSHIP
47 Simple and advanced filters with CALCULATE using FITLER function
Time Intelligence Functions
48 What are time intelligence functions
49 Why you must have a date table
50 The best time comparison function – DATEADD
51 DATEADD vs SAMEPERIODLASTYEAR
52 Aggregations – YTD, QTD, MTD
53 Time comparisons – PREVIOUSMONTH, PARALLELPERIOD
54 Information – OPENINGBALANCEMONTH, STARTOFMONTH, ENDOFYEAR
55 DATESBETWEEN, DATESINPERIOD, DATEDIFF functions
56 Practical examples using time intelligence functions
Advanced Techniques For Variables
57 Understanding how variables are evaluated
58 Using table variables to simplify ultimate formulas
59 Using variables to clean up multiple measures
Creating Virtual Relationships
60 When to create virtual vs physical relationships
61 TREATAS
62 How does USERELATIONSHIP compare
Advanced Table Functions
63 How to manipulate virtual tables using table functions
64 ADDCOLUMNS
65 SUMMARIZE
66 You can also combine table functions together
67 CROSSJOIN
68 ROW
69 UNION
70 CALCULATETABLE
71 INTERSECT & EXCEPT
72 SUMMARIZECOLUMNS
73 GENERATE
74 GROUPBY
Ranking Functions
75 RANKX
76 TOPN
77 Practical examples for using ranking functions
Advanced DAX Formula Combination Examples
78 This is where the measure branching methodology really shines
79 Compare multiple quarterly sales on each other
80 Using virtual tables with COUNTROWS
81 Budgeting allocation algorithm
82 Fixing errors with your totals effeciently
Resolve the captcha to access the links!