English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 11.5 Hours | 3.97 GB
Master the DAX language and build advanced Power BI & data analysis skills with a top business intelligence instructor
For users looking to take their Power BI skills to the next level, mastering Data Analysis Expressions (DAX) is a MUST. In this hands-on, project-based course, you’ll put your DAX skills to the test and apply what you learn to some of the toughest real-world business intelligence use cases.
You’ll be playing the role of Business Intelligence Analyst for Maven Roasters, a small-batch coffee chain based in New York City. Your goal? Use Power BI and DAX to answer key questions and deliver data-driven insights for the business, by analyzing product inventory, repeat customers, store-level performance, and everything in between.
We’ll start by quickly reviewing prerequisite topics (evaluation context, filter flow, basic measures, etc.), then dive behind the scenes to explore how the DAX formula and storage engines actually work, including query evaluation, columnar structures, and VertiPaq compression and encoding.
From there we’ll cover helpful tips and best practices for DAX power users, including variables, comments, query formatting and error handling, then dive deep into scalar functions, iterators, advanced time intelligence, calculated table joins, relationship functions, and much, much more. Last but not least, we’ll share a sneak peek into performance tuning and DAX query optimization using DAX Studio and Power BI’s Performance Analyzer tool.
If you’re looking to build expert-level data analysis skills using DAX and Power BI, this is the course for you!
- Prerequisite Skills Review
- Review the core DAX concepts you should already know, including evaluation context, filter flow, measures, etc.
- Course Project Intro
- Introduce the Maven Roasters course project, and build the data model that we’ll analyze in Power BI Desktop
- The DAX Engines
- Understand the DAX formula and storage engines, data types, VertiPaq encoding & compression methods, etc.
- DAX Tips & Best Practices
- Review helpful tips for formatting DAX queries, adding comments, handling errors, and using DAX variables
- Scalar Functions
- Explore common DAX scalar functions, including rounding, information, conversion, and logical functions
- Advanced CALCULATE
- Review CALCULATE modifiers, context transition, interactions with tables, etc.
- Table & Filter Functions
- Create calculated tables, review common DAX filter functions, generate new data, etc.
- Calculated Table Joins
- Create calculated joins between physical and virtual tables in Power BI (UNION, EXCEPT, INTERSECT, etc.)
- Relationship Functions
- Explore expanded tables, physical & virtual relationships, common relationship functions, etc.
- Iterator (“X”) Functions
- Explore iterator cardinality, nested iterators, context transition, RANKX, DAX calculation granularity, etc.
- Advanced Time Intelligence
- Build date tables with DAX, compare custom time periods, manage fiscal calendars, etc.
- SNEAK PEEK: Performance Tuning
- Introduce DAX Studio, the Power BI performance analyzer, and common DAX optimization techniques
What you’ll learn
- Master Data Analysis Expressions (DAX) for Power BI Desktop
- Analyze real-world projects & business intelligence use cases
- Learn the same tools used by top analytics & business intelligence professionals
- Test your skills with hands-on demos & assignments (with step-by-step solutions)
Table of Contents
Getting Started
1 Course Structure & Outline
2 READ ME Important Notes for New Students
3 DOWNLOAD Course Resources
4 Setting Expectations
5 Helpful Resources
Prerequisite Skills Review
6 Introduction
7 Data Modeling Fundamentals
8 Basic DAX Review
Introducing the Course Project
9 Introducing the Course Project
10 NOTE Power BI Options & Settings
11 Maven Roasters Data Model Setup
12 ASSIGNMENT Getting Familiar with the Data Model
The DAX Engines
13 Introduction
14 DAX Formula & Storage Engines
15 DAX Query Evaluation In Depth
16 Data & Storage Types
17 VertiPaq Columnar Data Structure
18 VertiPaq Compression & Encoding
19 Value Encoding
20 Hash Encoding
21 Run Length Encoding
22 VertiPaq Relationships
23 Summary DAX Engines
Tips & Best Practices
24 Introduction
25 DAX Shortcuts
26 Formatting Best Practices
27 DAX Evaluation Order
28 Commenting Your Code
29 PRO TIP Dedicated Measure Table
30 Error Handling
31 Creating & Using DAX Variables
32 Variable Evaluation Order
33 PRO TIP Using Variables to Text & Debug DAX
Scalar Functions
34 Introduction
35 Common Scalar Functions
36 Aggregation Functions
37 PRO TIP SUM & SUMX
38 Rounding Functions
39 ASSIGNMENT Rounding Functions
40 SOLUTION Rounding Functions
41 Information Functions
42 Conversion Functions
43 ASSIGNMENT Conversion Functions
44 SOLUTION Conversion Functions
45 Logical Functions SWITCH
46 Logical Functions COALESCE
47 ASSIGNMENT Logical Functions
48 SOLUTION Logical Functions
Advanced CALCULATE
49 Introduction
50 Expanded Tables
51 Context Transition
52 Evaluation Order
53 CALCULATE Modifiers
54 REMOVEFILTERS
55 KEEPFILTERS
56 ASSIGNMENT Advanced CALCULATE
57 SOLUTION Advanced CALCULATE
58 PRO TIP Common CALCULATE Patterns
Table & Filter Functions
59 Introduction
60 Common Table & Filter Functions
61 Review Calculated Tables
62 DISTINCT
63 VALUES
64 ASSIGNMENT VALUES & DISTINCT
65 SOLUTION VALUES & DISTINCT
66 SELECTEDVALUE
67 ALLEXCEPT
68 ASSIGNMENT ALLEXCEPT
69 SOLUTION ALLEXCEPT
70 ALLSELECTED
71 ASSIGNMENT ALLSELECTED
72 SOLUTION ALLSELECTED
73 SELECTCOLUMNS
74 ADDCOLUMNS
75 SUMMARIZE
76 ASSIGNMENT SUMMARIZE
77 SOLUTION SUMMARIZE
78 Generating New Data
79 ROW
80 DATATABLE
81 GENERATESERIES
82 The Table Constructor
83 ASSIGNMENT Generating Data
84 SOLUTION Generating Data
Calculated Table Joins
85 Introduction
86 CROSSJOIN
87 UNION
88 EXCEPT
89 INTERSECT
90 ASSIGNMENT Calculated Table Joins
91 SOLUTION Calculated Table Joins
Relationship Functions
92 Introduction
93 Physical vs. Virtual Relationships
94 RELATED
95 RELATEDTABLE
96 ASSIGNMENT RELATED & RELATEDTABLE
97 SOLUTION RELATED & RELATEDTABLE
98 USERELATIONSHIPS
99 CROSSFILTER
100 ASSIGNMENT CROSSFILTER
101 SOLUTION CROSSFILTER
102 TREATAS
103 ASSIGNMENT Sales Targets (TREATAS)
104 SOLUTION Sales Targets (TREATAS)
Iterator Functions
105 Introduction
106 Review Basic Iterators
107 Iterator Cardinality
108 CONCATENATEX
109 PRO TIP Adding Dynamic Labels
110 ASSIGNMENT CONCATENATEX
111 SOLUTION CONCATENATEX
112 AVERAGEX
113 PRO TIP Moving Averages
114 ASSIGNMENT Moving Averages
115 SOLUTION Moving Averages
116 RANKX
117 ASSIGNMENT RANKX
118 SOLUTION RANKX
Advanced Time Intelligence
119 Introduction
120 Automatic Date Tables
121 Date Table Requirements
122 CALENDAR
123 CALENDARAUTO
124 PRO TIP Building a Reusable Date Table
125 Date Formatting
126 ASSIGNMENT Date Formatting
127 SOLUTION Date Formatting
128 Common Time Intelligence Functions
129 PARALLELPERIOD
130 PREVIOUSQUARTER
131 SAMEPERIODLASTYEAR
132 ASSIGNMENT Time Periods
133 SOLUTION Time Periods
134 Week-based Calculations
135 Previous Fiscal Week
136 Fiscal Period to Date
137 Fiscal Previous Period
138 ASSIGNMENT 4-5-4 Calendar
139 SOLUTION 4-5-4 Calendar
Sneak Peek Performance Tuning
140 Introduction
141 Power BI Performance Analyzer
142 PRO TIP Copy Query
143 DAX Studio
144 DAX Optimization Workflow
Wrapping Up
145 BONUS LECTURE More from Maven Analytics
Resolve the captcha to access the links!