Master DAX fundamentals : Power BI, Power Pivot & SSAS

Name: Master DAX fundamentals : Power BI, Power Pivot & SSAS
Rating: 4.0 (885 reviews)

The Fundamentals of Data Analysis and Modelling with DAX for Power BI, Power Pivot and Analysis Services

Created byPaula Guilfoyle CPA

Last updated 5/2020

English

What you'll learn

Explain and use Calculated Columns and Measures
Explain and use aggregation functions and expressions in DAX such as SUM and COUNT
Explain and use table functions such as Filter and RelatedTable
Explain and use iteration pairs such as SUMX and COUNTX
Set up Variables
Work with IF statements and DAX SWITCH function
Describe the fundamentals of how DAX works
Explain Row context and nested Row context
Explain Filter context
Use the ALL function to remove filters
Use the Calculate function to modify and replace basic filters
Explain and demonstrate how Relationships and DAX work together
Discuss the need for a DATE table
Use DAX to create a DATE table using CalendarAuto and other DAX functions
Use and explain Time Intelligence Functions such as TOTALTYD, SAMEPERIODLASTYEAR and more
Calculate Moving totals and Rolling Totals
Carry out Basic to Intermediate level DAX analysis

Course content

5 sections • 47 lectures • 3h 5m total length

Introduction2:03
A quick introduction to this course Master DAX Fundamentals: Power BI, Power Pivot and SSAS
How to take this course1:27
Please read the attached as it contains important information on how to take this course to get the most out of it
Before you forget0:12

What is DAX4:43
A brief history and overview of Data Analysis Expressions (DAX)
Calculated Columns and Measures1:56
There are two types of calculations in DAX. Calculated Columns and Measures. We will look at both of these in this video
SUM and SUMX9:59
learn about SUM and SUMX, be introduced to RELATED and iterators
Count and CountX10:59
learn COUNT and COUNTX and see how calculated columns and measures differ, and learn when it is best to use each
Currency Data Type and the need to ROUND in DAX1:19
When working with the Data type Currency in DAX, you need to ensure that all of the values are to the correct decimal place, for this you can use ROUND
DAX DIVIDE expression4:17
the DAX DIVIDE function will allow you deal with errors when dividing one value by another, Learn how to use DIVIDE in this video.
When you need a BLANK for the right data type1:42
Are you familiar with the use of "" to get a blank in Excel? Well in DAX you need to use the BLANK expression to ensure that your blank is of the correct data type
Related and Related Table6:11
Related and related table will allow you take values from tables joined by relationships. In this video you will be introduced to related table and will we will at related in more detail
MIN and MAX2:58
If you are looking for the minimum value or the maximum value then DAX MIN and MAX expressions are required. This video includes the updated DAX syntax for MAX in versions 2016 and after, as well as the original syntax.
MINX and MAXX are also covered in this video
Other Expressions1:19
In this video you will be introduced to a number of iterators and pairs
IF Statements4:38
learn how to carry out an IF statement in DAX
Conditional statement (AND OR and IN)13:43
Master DAX conditional logic with and, or, and the in function in Power BI, and learn to create calculated columns using and, or, in syntax and the n statement.
Classification columns using AND OR and IN5:01
DAX Variables4:24
Learn how to create a Variable to be used in DAX functions and expressions
DAX SWITCH expression4:04
Learn how you can use the DAX SWITCH expression to replace nested IF statements
Working with Text in DAX9:19
There are many TEXT expressions available in DAX, Expressions that will also you format, clean and extract data from TEXT. In this video we will cover many of these TEXT expressions
Section Wrap up1:10
An overview of section 1.
Section 1 Activities0:12

How DAX works7:34
Table Functions7:06
Ignore Filters6:56
Combining Table Functions2:57
Nested Row Context6:22
Learn to use DAX EARLIER expression
Calculate8:36
CALCULATE is known as one of the most powerful functions in DAX. Find out why in this video
More on Calculate3:30
Learn to use CALCULATE with two filters
DAX and Relationships5:13
Explore how relationships shape DAX calculations and filter context across one-to-many links between suppliers, products, sales, and customers, using related and relatedtable functions.
Section 2 Wrap up1:34
Section 2 Activity0:23

Introduction to Time Intelligence1:29
a quick overview of what to expect in this section on DAX Time Intelligence
Creating a Date Table4:45
learn how to use DAX to create a DATE table, which is used for time intelligence functions
Multiple Date Tables4:41
Learn how to address multiple date columns and tables when using DAX
Marking a Date Table0:58
in some user interfaces you need to mark a date table as a date table. Don't forget to do this
Year to Date Calculations5:29
In this video you will learn how to use DAX TOTALYTD, TOTALQTD and TOTALMTD expressions
How Time Intelligence Works5:20
Following on from the previous lesson, in this video you will learn how these time intelligence functions work using Calculate.
Looking Back in Time3:42
Sometimes you need to show same period last year, with dax time intelligence this is rather easy
Datesadd4:41
learn more about how time intelligence works using datesadd
Parallel Periods2:16
Moving Totals3:09
Overall Total2:54
Learn how to calculate the overall total ignoring any time period
Time Intelligence conclusion0:36
Section 3 Activities0:23

Requirements

You should be familiar with either Power Pivot, Power BI or Analysis Services
You should have a want to learn DAX (Data Analysis Expressions)
Have an interest in Data Modeling

Description

Data Analysis and data modelling made easy with Data Analysis eXpressions. DAX can be used in Power BI, Power Pivot and Analysis Services.

Analyse and Model your data with in Power BI, Excels Power Pivot and Analysis Services with Data Analysis eXpressions.

This is a hands on course

Course Overview

In the first section of this course you will be introduced to the use of the most common DAX expressions, such as SUM and SUMX, COUNT, ROUND, DIVIDE, MIN, MAX, IF Statements, TEXT expressions, Variables and More

In the next section of this course you will learn the fundamentals of how DAX works. This section will include both theory and examples and it is of utmost importance that you understand how DAX works. We will discuss Row context and Filter context, ALL, FILTER, RELATEDTABLE, CALCULATE, EARLIER, Relationships and much more.

In the third section of this course you will learn about Time Intelligence and Date Tables. We will look at expressions such as TOTALYTD, SAMEPERIODLASTYEAR, DATESADD, DATESBETWEEN, CALENDERAUTO, and we will explore how many of these functions work using CALCULATE

Each section of this course contains Activities to complete. These Activities are of utmost importance. When it comes to learning DAX, practice is a must.

Who this course is for:

Analysis Services Users
Power BI Users
Excels Power Pivot Users
Those that need to model data using SSAS, Power BI or Power Pivot
Data Analyst, Finance and Accounting Staff, Marketing Professionals, Business Managers and all those involved with reporting
This course is NOT for you if you are already comfortable working with basic and intermediate level DAX

Master DAX fundamentals : Power BI, Power Pivot & SSAS

What you'll learn

Explore related topics

Course content