GRC-102: Conquering DAX 2019

RM4,000.00

Data Analysis Expressions (DAX) is the native formula and query language for Microsoft PowerPivot, Microsoft Power BI Desktop and SQL Server Analysis Services Tabular models.
 
DAX 2019 is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create reports and  new information from data already in your model.
 
Training Duration: 5 Days
Certificate Of Completion Available
Group Private Class
VILT Class Available
HRDCorp SBL-Khas Claimable
 
Download Course Outline
 
For more details, may check out the entire series and blogs at Honing Your Skills On Microsoft Power Platform

This GRC-102: Conquering DAX 2019 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. The training is aimed at users of Microsoft Power BI, Power Pivot for Excel, and at Analysis Services developers that want to learn and master the DAX language. This DAX training course covers the latest version of DAX 2019.

PRO Tip: Why DAX Is Underrated And Why You Should Learn?
After completing this course, students will be able to:

Understand all the features of the DAX language

Write formulas for common and advanced scenarios
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

GRC-102: Conquering DAX 2019

WHAT WILL YOU LEARN

OBJECTIVES

PREREQUISITES

COURSE MODULE

Recommended Courses Upon Completion

Recommended Reading

For a private in-house, please ask us for a quotation.

Get Ahead of the Competition with a FREE 1-Day Training for Your Company with our GRC-X Premium