GRC-102: Conquering DAX 2019

After completing this course, students will be able to:

• Understand all the features of the DAX language
• Write formulas for common and advanced scenarios

Attendees need to have a basic knowledge of the data modeling in Power Pivot for Excel, or Power BI Desktop, or Analysis Services Tabular modeling.

Module 1:    Introduction to DAX

• What is DAX?
• DAX data types
• Calculated columns
• Measures
• Aggregation functions
• Counting values
• Conditional functions
• Handling errors
• Using variables
• Mathematical functions
• Relational functions

Module 2:    Table Functions

• Introduction to table functions
• Filtering a table
• Ignoring filters
• Mixing filters
• DISTINCT Function
• How many values for a column?
• ALLSELECTED function
• RELATEDTABLE function
• Tables and relationships
• Tables with one row and one column
• Table variables

Module 3:    Evaluation Contexts 

• Introduction to evaluation contexts
• Filter context
• Row context
• Context errors
• Filtering a table
• Using RELATED in a row context
• Ranking by price
• Evaluation contexts and relationships
• Filters and relationships

Module 4:    CALCULATE Function

• Introduction to CALCULATE function
• CALCULATE function examples
• CALCULATE function recap
• What is a filter context?
• KEEPFILTERS function
• CALCULATE operators
• Use one column only in a compact syntax
• Variables and evaluation contexts

Module 5:    Advanced Evaluation Contexts

• CALCULATE modifiers
• USERELATIONSHIP function
• CROSSFILTER function
• ALL function
• ALLSELECTED function
• KEEPFILTERS function
• Context transition
• Circular dependency
• CALCULATE execution order

Module 6:    Iterators

• Working with iterators
• MINX and MAXX functions
• Useful iterators
• RANKX function
• ISINSCOPE function

Module 7:    Building a Date Table

• Introduction to date tables
• Auto Date/Time
• CALENDARAUTO function
• Mark as date table
• Using multiple dates

Module 8:    Time Intelligence in DAX

• What is time intelligence?
• Time intelligence functions
• DATEADD function
• DATESINPERIOD function
• Running total
• Mixing time intelligence functions
• Semi-additive measures
• Calculation over weeks

Module 9:    Hierarchies in DAX

• What are hierarchies?
• FILTER and CROSSFILTER function
• Percentages over hierarchies
• Parent-child hierarchies

Module 10:    Data Lineage and TREATAS

• What is data lineage?
• TREATAS function

Module 11:    Expanded Tables

• Filters are tables!
• Difference between base tables and expanded tables
• Filtering a column

Module 12:    Arbitrarily Shaped Filters

• What are arbitrarily shaped filters?
• Example of an arbitrarily shaped filter

Module 13:    ALLSELECTED and Shadow Filter Contexts

• ALLSELECTED function revisited
• Shadow filter contexts

Module 14:    Segmentation

• Static segmentation
• Circular dependency in calculated tables
• Dynamic segmentation

Module 15:    Many-to-many Relationships

• How to handle many-to-many relationships
• Bidirectional filtering
• Expanded table filtering
• Comparison of the different techniques

Module 16:    Ambiguity and Bidirectional Filters

• Understanding ambiguity

Module 17:    Relationships at Different Granularities

• Working at different granularity
• Using TREATAS function
• Calculated tables to slice dimensions
• Leveraging weak relationships
• Scenario recap
• Checking granularity in the report
• Hiding or reallocating

Module 18:    Querying with DAX

• Working with tables and queries
• EVALUATE syntax
• CALCULATETABLE function
• SELECTCOLUMNS function
• SUMMARIZE function
• SUMMARIZECOLUMNS function
• CROSSJOIN function
• TOPN and GENERATE functions
• ROW and DATATABLE functions
• Tables and relationships
• UNION, INTERSECT and EXCEPT functions
• GROUPBY functions
• Query measures

Lab Exercises: 

• Lab 01
  • First steps with DAX
  • Average sales per customer
  • Average delivery time
  • Last update of customer
  • Working days
  • Discount categories

• Lab 02
  • Percentage of sales
  • Delivery working days
  • Sales of products in the first week
  • Customers with children

• Lab 03
  • Nested iterators
  • Customers in North America (BASIC)
  • Create a parameter table

• Lab 04
  • Sales of red and blue products
  • Understanding CALCULATE
  • Sales of blue products
  • Customers in North America (ADVANCED)
  • Computing percentages

• Lab 05
  • Correct sales of grey products
  • Best customers
  • Customers buying many products
  • Large sales
  • Percentage of customers
  • Counting spikes

• Lab 06
  • Ranking customers (static)
  • Ranking customers (dynamic)
  • Date with the highest sales
  • Moving average

• Lab 07
  • Running total
  • Comparison YOY%
  • Sales in the first three months
  • Semi-additive calculations

• Lab 08
  • Distinct count of countries
  • Sales quantity greater than two

• Lab 09
  • Static segmentation

• Lab 10
  • Many-to-many relationships

• Lab 11
  • Sales by year
  • Filtering and grouping sales
  • Using TOPN and GENERATE
  • Sales of top customers
  • Sales of top three colors

• Bonus Lab
  • Same product sales
  • Commentary on report
  • New customers

GRC-101: An Analytical Journey with Microsoft Power BI

This GRC-101: An Analytical Journey with Microsoft Power BI custom developed instructor-led adventurous workshop is designed for audiences to be equipped with the necessary knowledge and skills to kickstart their analytical journey with Microsoft Power BI.

GRC-104: Intensive Data Modeling For Microsoft Power BI Creators

This intensive Power BI data modeling workshop is a complete course about building the most optimal data models for your Power BI reports. It introduces the audience to the basic techniques of shaping data models in Power BI. It offers many real-world examples that will help you look at your reports in a different way – pretty much like experienced data modelers do.

GRC-105: Microsoft Power BI Advanced Dashboard Design Concepts And Strategies

This 2-day Power BI dashboard design course is designed for Power BI creators who wish to learn how to create beautiful and effective dashboards, while avoiding all the common design pitfalls and mistakes.

GRC-107: Building A Data Literate Culture In Your Organization

When information becomes a valuable asset, data becomes a strategic priority. Data is becoming a competitive differentiator for top organizations as they focus on digital transformation while still in the early stages of adoption. Data serves as a significant catalyst for a company's digitalization and transformation activities.

GRC-112: Microsoft Power BI Data Modeling with DAX

This GRC-112: Microsoft Power BI Data Modeling with DAX workshop is a complete course about the DAX language. DAX is the native language of Power BI, Power Pivot for Excel, and SSAS Tabular models in Microsoft SQL Server Analysis Services.

Learning DAX (Data Analysis Expressions) offers numerous benefits for individuals and businesses alike, as it empowers users to extract valuable insights from data.  Explore more about DAX with our DAX related blogs:

• Master The Art Of Learning Dax In 2023
• Why DAX Is Underrated And Why You Should Learn?
• The Future Of Enterprise Analytics Is Power BI And Azure