DAX Deep Dive - From Zero to DAX Hero

Overview:

Dive into DAX, the key language for Power BI, Power Pivot for Excel, and Microsoft's SSAS Tabular models. This course is crafted for those using these tools, aiming to simplify DAX and enable you to address both basic and advanced business scenarios with ease. Learn, apply, and master DAX together.

Online

Who Should Do This Course?

This curriculum is perfect for data professionals, BI developers, and analysts who wish to dive deep into the world of DAX, enhancing their analytics capabilities. Whether you're already familiar with Power BI or seeking to leverage DAX's potential in other Microsoft tools, this course is your golden ticket to mastery.

Why Choose This Course:

Learn DAX from the ground up under the guidance of a Microsoft Certified Trainer. Experience real-world scenarios, tackle DAX challenges, and elevate your data analysis skills to new horizons. Invest in the heart of modern data insights; Choosing this course means you're investing in the future of data analytics.

About the Instructor:

As a Microsoft Certified Trainer and MVP, Vahid has over 14 years of experience in the fields of data analysis, business intelligence, and Microsoft technologies. As an experienced Blogger, Trainer, Speaker, and Consultant, his insights can be explored further through his articles at Vahid's Blog and tutorials on YouTube. Additionally, as a recognized voice in the industry, Vahid actively contributes to international events - his page on Sessionize.

Contents:

1. Introduction to DAX:

   • What is DAX?

   • Importance of DAX in Power BI and other Microsoft tools

   • Differences between DAX and other formula languages

   • DAX operators and data types

   • Calculated columns

   • Measures

   • Handling Errors

2. Understanding Basic DAX Functions:

   • Aggregation functions

   • Mathematical functions

   • Text Functions

   • Variables in DAX

   • Relational functions

3. Table functions:

   • Filter and how to mix and ignore filters

   • Introducing ALL, ALLEXPECT, ALLSELECTED

   • Understand VALUES, DISTINCT, and Blank

   • Tables and relationships

4. Evaluation Contexts:

   • Filter Context

   • Row Context

   • Context interactions

   • Context errors

5. CALCULATE function:

   • Introducing CALCULATE

   • Introducing KEEPFILTERS

   • CALCULATE Operators

   • CALCULATE modifiers (USERRELATIONSHIP, CROSSFILTER, KEEPFILTERS, ALL)

   • Introducing context transition

6. Variable:

   • How to create VAR in DAX

   • Scope of VAR

   • Table variable

   • Debugging using variables

7. Iterators:

   • Introducing iterators

   • Using CONCATENATEX

   • Understanding iterators and table output

   • Introducing MINX, MAXX, RANKX, and ISINSCOPE

8. Create DATE table:

   • Automatic Date/Time in Power BI

   • Using CALENDAR and CALENDARAUTO

   • Mark as a date table

   • Create a date table with Bravo

9. Time intelligence in DAX:

   • Introducing time intelligence

   • Introducing DATEADD, DATESINPERIOD, DATESBETWEEN

   • Introducing LASTDATE, LASTNONBLANK, FIRSTDATE, LASTDATE, FIRSTNONBLANK

   • Computing Running Total, YoY, MoM, WoW

   • Introducing DATESYTD, DATESQTD, DATESMTD, TOTALYTD, TOTALQTD, TOTALMTD

10. Hierarchies in DAX:

    • Handling parent/child hierarchies

    • Computing percentages over hierarchies

    • Queries in DAX:

    • Introducing DAX Studio

    • Understanding EVALUATE

    • Using VAR and MEASURE in DEFINE

    • Introducing ROW, TOPN, GENERATE, GROUPBY, ORDERBY

11. Working with tables:

    • Using CALCULATETABLE

    • Understanding EVALUATE and DEFINE

    • Manipulating tables using ADDCOLUMNS, SUMMARIZE, CROSSJOIN, UNION, INTERSECT, EXCEPT

    • Calculate tables using SELECTECOLUMNS, DATATABLE, GENERATESERIES

12. Relationships:

    • Introducing TREATAS

    • How to handle Many to Many relationships

    • Bidirectional cross-filters

    • Understand Ambiguity and handle that

13. Advanced DAX concepts:

    • Understanding RELATED

    • Understanding the difference between table filters and column filters

    • Understanding ALLSELECTED and shadow filter contexts

    • Circular dependency

14. Calculation groups:

    • Introducing calculation groups

    • Creating calculation groups

    • Changing the format string

15. Optimize Performance:

    • Analyzing server timings and query plan information with DAX Studio

    • Identifying bottlenecks in the storage engine or formula engine

    • Optimizing bottlenecks in DAX expressions

16. Patterns in DAX:

    • Introduction to common DAX patterns

    • Mastering pattern-based solutions

17. DAX Tools and Other Resources:

    • DAX Formatter

    • Writing C# script in Tabular Editor

    • Other DAX Reference