GRC-112: Microsoft Power BI Data Modeling with DAX

Name: GRC-112: Microsoft Power BI Data Modeling with DAX
Brand: GemRain

RM4,000.00

Basic data models are simple to construct using Power BI Desktop. However, Data Analysis Expressions are required if you wish to report across time periods—for example, to display year-over-year growth or to compare sales or other data by month or quarter (DAX). DAX is a programming language for creating Power BI calculations that extend your data model.

Learn how to use DAX aggregate functions and add calculated columns, build measurements, and use DAX logical and filter functions and recommended practices for data model construction and optimization.

Whether you're using Power BI Desktop, Power Pivot in Excel, or Analysis Services, learning Data Analysis Expressions (DAX) is essential to creating effective data models.

Training Duration: 5 Days

Certificate Of Completion Available
Group Private Class
VILT Class Available
HRDCorp SBL-Khas Claimable

Download Course Outline

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.

This Power BI training for DAX is aimed at users of Power BI, Power Pivot for Excel, and at Analysis Services developers that want to learn and master the DAX language. This course covers the latest version of DAX 2019.

This 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.
After completing this course, students will be able to:

Understand all the features of the DAX language

Write formulas for common and advanced scenarios

Learn the vast techniques of shaping and building a correct data model to be used in Power BI.

Understand the common challenges and mistakes that people have in creating a data model.

Learn the most optimum way of building a sustainable data model for Power BI reports
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:   Iterators

Working with iterators

MINX and MAXX functions

Useful iterators

RANKX function

ISINSCOPE function

Module 6:   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 7:   Hierarchies in DAX

What are hierarchies?

FILTER and CROSSFILTER function

Percentages over hierarchies

Parent-child hierarchies

Module 8:   ALLSELECTED and Shadow Filter Contexts

ALLSELECTED function revisited

Shadow filter contexts

Module 9:   Segmentation

Static segmentation

Circular dependency in calculated tables

Dynamic segmentation

Module 10:   Many-to-many Relationships

How to handle many-to-many relationships

Bidirectional filtering

Expanded table filtering

Comparison of the different techniques

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

Module 11:   Introduction to Data Modeling

What is Data Modeling?

Analytical limits

Increasing the analytical power

Introducing the facets of data modeling

Leveraging the data model

Normalization and denormalization

Facts and dimensions

Introducing star schemas

Chains of relationships

How many dimensions?

Why data modeling is useful?

Lab 01 – Basic data modeling

Module 12:   Header / Detail Tables

Introduction to header / detail schemas

Bidirectional filtering is not the way!

Denormalizing the discount

Back to star schema

Lab 02 – Header / detail

Module 13:   Working with Date and Time

Date attributes in the fact table

Building a date dimension

CALENDARAUTO function

The model with a date dimension

Automatic date grouping in Power BI

Quick calculations in Power BI Desktop

Disable automatic date columns

Handling multiple dates

Multiple date tables

Multiple date tables with multiple fact tables

Multiple relationships with date

Handling date and time

Computing with working days

Create a holidays table (one country)

Create a holidays table (multiple countries)

Weekends are not always the same!

Handling special periods of the year

Non-overlapping periods in the model

Non-overlapping special periods

Overlapping period measure

Lab 03 – Working with date and time

Module 14:   Using Snapshot Tables

What is a snapshot table?

Sales versus inventory in the same model

Non-additive measures

LASTDATE function does not work here!

Optimizing performance

Snapshots and granularity

Transition matrix

Parameter tables

Lab 04 – Snapshots

Module 15:   Analyzing Date and Time Intervals

What are intervals?

Solving with DAX… too complex!

Changing granularity

Split hours AND amount!

Analyzing active events

Open orders: the starting model

Open orders with DAX

Open orders in a snapshot table

Events with different durations

Daily salary in DAX

Precompute the values

Lab 05 – Date and time interval

Module 16:   Many-to-Many Relationships

What are many-to-many relationships?

Possible solutions to this complex scenario

Bidirectional filtering

Using CROSSFILTER function

Using expanded table filtering

CROSSFILTER versus expanded tables

Understanding non-additivity

Cascading many-to-many

Lab 06 – Many-to-many relationships

Module 17:   Working with Different Granularities

Dimensions define granularity

Analyzing budget data

Reduce granularity on all the tables

Using DAX to move the filters

Filtering through relationships

Using the correct column to slice

Leveraging relationships

Checking granularity in the report

Hiding or reallocating?

Lab 07 – Granularity
GRC-102: Conquering DAX 2019

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.

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.

PL-300T00: Microsoft Power BI Data Analyst

This PL-300T00: Microsoft Power BI Data Analyst course will discuss the various methods and best practices that are in line with business and technical requirements for modeling, visualizing, and analyzing data with Power BI.
Turn your data into immediate impact.

Do more with less using an end-to-end BI platform to create a single source of truth, uncover more powerful insights, and translate them into impact with these recommended resources:

The Common Questions and Answers for Power BI

Why POWER BI makes your reporting life easier?

Low-Code Datamart with Microsoft Power BI

The Future Of Enterprise Analytics Is Power BI And Azure

Microsoft Power BI: Creating Meaningful Data Reports

Real-Time Streaming In Microsoft Power BI

How Do You Extract The True Value Of Your Data With Power BI?

Master The Art Of Learning Dax In 2023

Why DAX Is Underrated And Why You Should Learn?

GRC-112: Microsoft Power BI Data Modeling with DAX

WHAT WILL YOU LEARN

OBJECTIVES

PREREQUISITES

COURSE MODULE

Recommended Courses Upon Completion

Recommended Resources

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