Mastering DAX & Data Modeling
Push Beyond Power Query’s User Interface and Solve Tricky Data Cleaning Problems.
You’ll learn to build robust excel / power bi models by creating dynamic queries using the M language.
Why Learn M?
When I started working with Power Query, I felt as if I found the “Genie”. It was way too simple to connect to gather data from multiple sources, clean it up and use that data in further analysis.
The best part – Power Query’s work is reusable. The next time anything changes, just hit refresh.
But as I started to solve more dense / trickier problems my refreshes failed. WHY?
Because something was hard coded or the query wasn’t dynamic enough to account for the data source change.
My Genie wasn’t good enough any more, until I powered my Genie with “M” a.k.a. Power Query’s functional language.
Using M, I have been able to…
- make my queries super dynamic.
- resolved and reported complex errors.
- and (most importantly) build very clean and performant data models (even with very complex data structures).
I’ve come a long way learning M and in the process I have built some of the most sophisticated data cleaning procedures, all thanks to M.
Here is an example of M v/s Power Query’s UI.
Expand Dynamic Columns videos and sales pitch as to why learning M is the real deal.
But Learning M is Hard!
Here is what makes M hard.
1. It has all the possible brackets denotations and special characters in the language. We aren’t designed to look at the code that looks like this.
2. To an excel user, it might look difficult to comprehend concepts like lists, records, tables, data types and manipulating these objects.
3. Power Query errors are not the most friendly errors.
👋 I am here to make that difficult easy for you, at-least relatively than trying to learn this on your own.
My Teaching Style
No Jargons, just simple English.
-
I hate jargons.
-
I don’t advocate nor use them in my course.
-
In this course, I’ll go the greatest extent to demystify DAX jargons.
Case Based Learning.
-
For almost all DAX concepts covered, I teach using a problem or case study.
-
This approach helps to visualize the application of the concept.
Developing a Problem-Solving Mindset.
-
Rather than mugging DAX functions one after the other, I focus on teaching you how to structure and solve problems.
-
I do teach DAX functions but more importantly, I teach you to structure problems so that you can solve problems beyond the cases discussed in this course.
Overview
By learning M in Power Query, you’ll gain the ability to efficiently transform and manipulate data, which is a valuable skill for data analysis, reporting, and data preparation tasks. It can save you time and help you work with data more effectively in various Microsoft applications.
What You’ll Learn
- Basic Syntax: Start with understanding the basic syntax of the M language. M is a functional language, which means it relies on functions and expressions to perform data transformations.
- Data Import: Learn how to import data from various sources such as databases, text files, Excel files, web services, and more into Power Query using M language expressions.
- Data Transformation: Master the techniques to clean, reshape, and transform data. M language provides a wide range of functions and operators for filtering, sorting, grouping, merging, pivoting, and aggregating data.
- Custom Functions: Discover how to create custom functions in M. This allows you to encapsulate complex data transformation logic and reuse it across different queries.
- Conditional Logic: Learn how to use conditional statements like if-then-else in M to perform different operations based on data conditions.
- Error Handling: Understand error handling techniques in M, like try…otherwise, to handle data errors gracefully and ensure your queries run smoothly.
- Advanced Techniques: Explore advanced techniques such as working with data types, custom columns, and managing query parameters.
Chapters
Part 1 - The Fundamentals
💡 In this part, you’ll understand the building blocks of DAX. What is DAX, Why is it important, How it works, Best Practices, Thumb Rules to keep in mind and more.
Complexity ●●●●○
Videos:
- What is DAX?
- Why do you need DAX (A Quick Exercise)
- Exercise Answers
- Implicit & Explicit Calculations
- Understanding Filter Context and 3 Stage Evaluation
- One – Many Relationships – Filter Context on Expanded Tables
- One to Many Relationship – Trick
- Working with Iterators – Part 1
- Working with iterators – Part 2
- Intervention – DAX Input and Output Types
- Intervention Video – DAX Input and Output Types
- Introducing CALCULATE – Context Modification
- Intervention Video – DAX Writing Conventions
- Intervention Video – Variables in DAX
- Getting Started with Time Intelligence
- Date / Calendar Table
- Date / Calendar Table for Fiscal Year
- Time Intelligence Calculations
- Time Intelligence Calculations for Partial Year Periods
- Managing Multiple Relationships
- Managing Multiple Relationships – Case Study
- Disconnected Tables with No Relationships
- Disconnected Tables – Practical Use
- Context Transition
- Context Transition and 3 Tricky Examples (gotchas)
- Getting Started with Calculation Groups
- DAX Lineage
- Intervention Video – DAX Shortcuts
Part 2 - Practical Applications of DAX
💡 In this part, you’ll understand the building blocks of DAX. What is DAX, Why is it important, How it works, Best Practices, Thumb Rules to keep in mind and more.
Complexity ●●●●○
Videos:
- What is DAX?
- Why do you need DAX (A Quick Exercise)
- Exercise Answers
- Implicit & Explicit Calculations
- Understanding Filter Context and 3 Stage Evaluation
- One – Many Relationships – Filter Context on Expanded Tables
- One to Many Relationship – Trick
- Working with Iterators – Part 1
- Working with iterators – Part 2
- Intervention – DAX Input and Output Types
- Intervention Video – DAX Input and Output Types
- Introducing CALCULATE – Context Modification
- Intervention Video – DAX Writing Conventions
- Intervention Video – Variables in DAX
- Getting Started with Time Intelligence
- Date / Calendar Table
- Date / Calendar Table for Fiscal Year
- Time Intelligence Calculations
- Time Intelligence Calculations for Partial Year Periods
- Managing Multiple Relationships
- Managing Multiple Relationships – Case Study
- Disconnected Tables with No Relationships
- Disconnected Tables – Practical Use
- Context Transition
- Context Transition and 3 Tricky Examples (gotchas)
- Getting Started with Calculation Groups
- DAX Lineage
- Intervention Video – DAX Shortcuts
Part 3 - DAX Function Library (Optional)
💡 In this part, you’ll understand the building blocks of DAX. What is DAX, Why is it important, How it works, Best Practices, Thumb Rules to keep in mind and more.
Complexity ●●●●○
Videos:
- What is DAX?
- Why do you need DAX (A Quick Exercise)
- Exercise Answers
- Implicit & Explicit Calculations
- Understanding Filter Context and 3 Stage Evaluation
- One – Many Relationships – Filter Context on Expanded Tables
- One to Many Relationship – Trick
- Working with Iterators – Part 1
- Working with iterators – Part 2
- Intervention – DAX Input and Output Types
- Intervention Video – DAX Input and Output Types
- Introducing CALCULATE – Context Modification
- Intervention Video – DAX Writing Conventions
- Intervention Video – Variables in DAX
- Getting Started with Time Intelligence
- Date / Calendar Table
- Date / Calendar Table for Fiscal Year
- Time Intelligence Calculations
- Time Intelligence Calculations for Partial Year Periods
- Managing Multiple Relationships
- Managing Multiple Relationships – Case Study
- Disconnected Tables with No Relationships
- Disconnected Tables – Practical Use
- Context Transition
- Context Transition and 3 Tricky Examples (gotchas)
- Getting Started with Calculation Groups
- DAX Lineage
- Intervention Video – DAX Shortcuts
Part 4 - DAX Debugging Techniques
💡 In this part, you’ll understand the building blocks of DAX. What is DAX, Why is it important, How it works, Best Practices, Thumb Rules to keep in mind and more.
Complexity ●●●●○
Videos:
- What is DAX?
- Why do you need DAX (A Quick Exercise)
- Exercise Answers
- Implicit & Explicit Calculations
- Understanding Filter Context and 3 Stage Evaluation
- One – Many Relationships – Filter Context on Expanded Tables
- One to Many Relationship – Trick
- Working with Iterators – Part 1
- Working with iterators – Part 2
- Intervention – DAX Input and Output Types
- Intervention Video – DAX Input and Output Types
- Introducing CALCULATE – Context Modification
- Intervention Video – DAX Writing Conventions
- Intervention Video – Variables in DAX
- Getting Started with Time Intelligence
- Date / Calendar Table
- Date / Calendar Table for Fiscal Year
- Time Intelligence Calculations
- Time Intelligence Calculations for Partial Year Periods
- Managing Multiple Relationships
- Managing Multiple Relationships – Case Study
- Disconnected Tables with No Relationships
- Disconnected Tables – Practical Use
- Context Transition
- Context Transition and 3 Tricky Examples (gotchas)
- Getting Started with Calculation Groups
- DAX Lineage
- Intervention Video – DAX Shortcuts
Part 6 - Case Study
💡 In this part, you’ll understand the building blocks of DAX. What is DAX, Why is it important, How it works, Best Practices, Thumb Rules to keep in mind and more.
Complexity ●●●●○
Videos:
- What is DAX?
- Why do you need DAX (A Quick Exercise)
- Exercise Answers
- Implicit & Explicit Calculations
- Understanding Filter Context and 3 Stage Evaluation
- One – Many Relationships – Filter Context on Expanded Tables
- One to Many Relationship – Trick
- Working with Iterators – Part 1
- Working with iterators – Part 2
- Intervention – DAX Input and Output Types
- Intervention Video – DAX Input and Output Types
- Introducing CALCULATE – Context Modification
- Intervention Video – DAX Writing Conventions
- Intervention Video – Variables in DAX
- Getting Started with Time Intelligence
- Date / Calendar Table
- Date / Calendar Table for Fiscal Year
- Time Intelligence Calculations
- Time Intelligence Calculations for Partial Year Periods
- Managing Multiple Relationships
- Managing Multiple Relationships – Case Study
- Disconnected Tables with No Relationships
- Disconnected Tables – Practical Use
- Context Transition
- Context Transition and 3 Tricky Examples (gotchas)
- Getting Started with Calculation Groups
- DAX Lineage
- Intervention Video – DAX Shortcuts
This course is NOT FOR YOU IF!
- You’re Looking for Quick Fixes: If you’re seeking quick solutions or shortcuts, this course may not be for you. It’s intended for those who are willing to invest time in learning the fundamentals.
- You’re Not Willing to Dedicate Time: This course may not be suitable if you have a very busy schedule and cannot commit the necessary time and effort for the lessons and assignments.
- You’re Not Comfortable with Technology: If you lack basic computer skills or are uncomfortable with using technology, this course may not be a good fit.
$199.99 – $385
Details
Beginner
12 hrs 43min
Updated: 3/12/2022
Prerequisites
Graphic Software
Laptop
Internet
Faq
How long is the course?
The course has approximately 50 + videos. Each video is about 12-15 mins long. Move over I’ll keep adding lessons to the course as and when I discover interesting aspects / case studies which are interesting enough and worth teaching.
Will I really master DAX after this course?
Short Answer – NO!
Watching videos is not enough, you have to commit to practice the lessons taught. Redoing it yourself, asking questions.
This course will give you a very clear understanding of how DAX works and how to solve simple to complex difficult problems using DAX and Data Modeling.
Building mastery over it involves practice, which is in your hands! 🙂
How can I ask questions?
Each video has a commenting section which you can use to ask questions. You also have the ability to paste your code or insert pictures to explain your problem more vividly.
WIll I get lifetime access to the course?
Yes, your course access will be for lifetime!
Testimonials
I am a Business Analyst and working on Power BI however wants to clean my core concepts for getting a much better grip on DAX. I hope Chandeep will become a trump card in understanding these concepts.
DAX is critical for my present role. I was struggling to learn strong basic understanding. I found this course.
Professional background in Accounting and Finance. Due to change in the technology and quick answer for business partners, I want to translate my excel skill to Power Bi and automate my work to focus more towards analytical skills. I am very excited and motivated to learn from you. Thank you!
I have actually subscribed to your excel course and after having gone through your lessons, I find it very good and enriching. Decided to subscribe the Dax course since I am also picking up power bi 🙂
I have been tinkering with Power BI for few months, but want to harness its potential learning through one of the best tutors (based on your Youtube Channel 🙂
I’m data consultant, I already used power bi a lot for simple needs, but the reason for enrolling this course is to learn and understand deeply DAX.
Offer / Buy
$199
Mastering M
Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium.
Lorem ipsum
Dolor Amet Sit
$249
Power Query + Mastering M
Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium.
Consecteture Amet
Lorem ipsum
Dolor Amet Sit
Best Seller
$279
DAX + M
Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium.
Consecteture Amet
Lorem ipsum
Dolor Amet Sit
$385
DAX + M + Power Query
Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium.