New Video Course: DAX and Calculations in Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

We are pleased to let you know that our DAX and Calculations in Power BI video course is now available. This course, like many other video courses we offer, is part of the Power BI from Rookie to Rock Star training. This is a course to consider if you need to create calculations for adding more analytics power to your Power BI solution.

This training is designed for data modelers, who have the data prepared to be modeled for analysis. Usually, people who take this course need to also take the Power Query video course to learn about the step before the modeling. In this training, you will learn detailed modeling practices of Power BI, and DAX unleashed.In this training, you will learn DAX from zero to hero. You will learn how to design the best model in Power BI with relationships, considering formatting and data types. You will learn about DAX which is the data modeling expression language in Power BI (and SSAS Tabular, and Power Pivot). You will learn from Simple DAX calculations to complex expressions and calculations for solving real-world challenges of a BI solution.The training continues with more focus on DAX; we will talk about the evaluation context in DAX, which defines the mindset and the way of thinking when you are writing DAX expressions. You will learn about DAX function categories such as Aggregation functions, Iterators, Filter functions, parent-child functions, time intelligence functions, functions dealing with relationships, etc. You will learn all scenarios through hands-on examples of real-world data.At the end of this training, you will be able to design the proper data model in Power BI, understand all relationship requirements and implement the right relationship, write complex DAX expressions for your analytics need, and put them all together to build the best model for your data analysis solution using Power BI.

Prerequisite:
There is no prerequisite for this course. However, it would be beneficial to watch the Power BI Essentials training video and Power Query video course before this to have familiarity with some concepts.

Link to the agenda and sample videos from the course

You can take this course either with Academy Membership and enjoy many other video courses about Power BI and AI on our website, or you can purchase the course separately. There is a great discount until the end of Jan 2020.

Video

Modules:

Introduction

In this short module, you will learn about the agenda, what the course covers, and what you need to get the sample dataset ready for the rest of the course.

Lessons

  • 0101 Agenda
  • 0102 Setup Dataset

Relationships

Relationships are one of the most important fundamental concepts to understand in a Power BI Model. In this module, you will learn what a relationship is, why we need it in a Power BI Model. You also learn about single vs. both-directional relationships and also active vs. inactive relationships.

Lessons

  • 0201 What is the Relationship?
  • 0202 Direction of the Relationship
  • 0203 Active or Inactive Relationships

Model Configuration

In this module, you will learn some of the basics of modeling which is related to fields and tables configuration. You will learn about formatting fields, creating hierarchies, setting up the default summarization, and some other configuration for your data model.

Lessons

  • 0301 Formatting Fields
  • 0302 Sorting a Column by Another Column
  • 0303 Hide Columns
  • 0304 Hierarchy
  • 0305 Auto Summarization
  • 0306 Other Configuration on Fields

DAX and Calculations

There are three types of calculations in Power BI. In this module, you will learn about all of the three calculation types, their differences and their scenario of usage. You will also learn an introduction to the DAX language and how the DAX expression can be written.

Lessons

  • 0401 Three Types of Calculation in Power BI
  • 0402 Introduction to DAX
  • 0403 DAX First Examples
  • 0404 Calculated Column Vs. Measure
  • 0405 Implicit Vs. Explicit Measures

Common Functions in DAX

There is a set of functions that commonly used in many DAX expressions. These are functions that work as a filter or iterator or even Calculate. In this module, you will learn what is an Iterator and how it can be used in a measure. You will also learn about filter functions such as ALL or Filter and how they can work combined with an iterator function. You will also learn examples of conditional sum implemented using the functions mentioned in this module. at the end of the module, you will also learn about Calculate which is one of the most common and powerful functions used in DAX.

Lessons

  • 0501 Iterator Functions: SUMX
  • 0502 ALL Function: Ignoring the Filter Context
  • 0503 Filter Function: Customized Filter Table
  • 0504 Conditional Sum, using SUMX, Filter, and IF
  • 0505 Calculate

Accessing other tables through Relationship

There are two functions that give you access to the data through existing relationships. In this module, you will learn about the Related function to access a value from the other side of the relationship, and also RelatedTable function to get a sub-table based on an existing relationship.

Lessons

  • 0601 Related Function
  • 0602 RelatedTable

Time Intelligence

Any BI system requires date-based analysis, which is what we call as Time Intelligence in Power BI. In this module, you will learn about DAX functions that deal with Time Intelligence. You will learn how to calculate year to date value, quarter to date, and how to make changes to make year to date to work with financial periods. You will learn how to compare this year’s value with last year’s value, or even with last month’s value, or even yesterday’s value. You will learn how to calculate the average rolling period. Functions that you will learn are included but not limited to DatesYTD, DatesQTD, SamePeriodLastYear, ParallelPeriod, DateAdd, DatesInPeriod, etc.

Lessons

  • 0701 Introduction to Time Intelligence
  • 0702 Default Vs. Custom Date Table
  • 0703 Calendar and CalendarAuto Functions
  • 0704 Year to Date Calculation
  • 0705 Financial Year to Date
  • 0706 Quarter to Date and Month to Date
  • 0707 Comparing this Year Vs. Last Year, Growth and Variance
  • 0708 Calculating Last Month’s value using ParallelPeriodPreview
  • 0709 DateAdd Function
  • 0710 Rolling 12 Months Calculation using DatesInPeriod
  • 0711 Calculating the Average in the Rolling 12 Months Period

Variables and Parameters

Using variables and parameters can enhance your DAX expression significantly. In this module, you will learn how to use variables, and how they are helpful in better performance and readability of your expression. You will also learn how you can make your expression dynamic using Parameters. Parameters that you can add using GUI, or even build the parameter table yourself.

Lessons

  • 0801 Variables for Better Performance and Readability
  • 0802 What-If Parameters in DAX
  • 0803 Parameter Table or Disconnected Table

Parent-Child Functions

Parsing an organizational hierarchy, or the hierarchy of chart of accounts is not as simple as normal hierarchies. In DAX, we have a set of parent-child functions that are helpful to parse this kind of hierarchies, when the levels of hierarchies are stored in ID, and Parent ID structure. In this module, you will learn how to work with these functions.

Lessons

  • 0901 Parsing Organizational Hierarchy using Parent-Child Functions

Relationship Functions

There are two functions that can change the behavior of relationships in Power BI. CrossFilter for changing the relationship to become both-directional, and UseRelationship to work with inactive relationships. In this module, you will learn about those functions and use cases of using them.

Lessons

  • 1001 Using CrossFilter to Change the Direction of Relationship
  • 1002 UseRelationship to Use an Inactive Relationship
FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

2 thoughts on “New Video Course: DAX and Calculations in Power BI

  • Hi Reza,
    Thank you so much for the book and videos. I learned a lot from them.
    There is something still not clear for me. If we use power BI do we still need to have data warehousing? Do we still need to use SSIS and SSAS?
    When I look for job, they ask for Power BI experience as well as data warehouse and ETL experience. I am not sure if these 2 are works together.

    • Hi Janet
      You can use Power BI without a data warehouse, especially because we have Power Query, Dataflow, and the shared dataset concept, Power BI can be an all in one solution, without the need for SSIS and SSAS, etc.
      However, some users are still using Power BI in a hybrid model, which means they use an ETL tool to load data into a data warehouse, then having SSAS model on top of it, and live connection from Power BI, for that implementation, you need to know the combination of those tools. You will see these requirements mainly in established BI teams in larger organizations that have been using BI for a while, and now switching the visualization layer with Power BI.
      Cheers
      Reza

Leave a Reply

%d bloggers like this: