Power BI - DAX Introduction

Pre-requisites

Participants should have at least Intermediate Excel and database user skills. They need: An understanding of basic data modelling. Familiarity with using formula syntax Experience using data models in Power Pivot or Power BI - having attended either our 1-day or 2 day Power BI Course Familiarity with Power BI, Excel and Database models

Who Should Attend

This course has been designed as an add-on to the standard Power BI courses for users who need to learn about advanced report generation. It covers topics and areas of the DAX language that are not in the standard 1 or 2 day courses. Attendees need to be familiar with generating reports in Power BI.

Learning Objectives

DAX is useful to different people, depending on their focus. The course aims to assist: Business Intelligence (BI) professionals and Power Users to implement DAX code in BI solutions of any size Casual Power BI users to author some DAX formulae in their self-service BI models Excel users to leverage DAX to author Power Pivot data models At the end of this course participants will have a detailed understanding of the DAX Language.

What is DAX

Understanding the data model. Understanding the direction of a relationship. DAX for Excel users. Cells versus tables.

Introducing DAX

Understanding DAX calculations. DAX data types. DAX operators. Understanding calculated columns and measures. Calculated columns. Measures. Variables. Handling errors in DAX expressions. Conversion errors. Arithmetical operations errors. Intercepting errors. Formatting DAX code. Common DAX functions. Aggregate functions. Logical functions. Information functions. Mathematical functions. Trigonometric functions. Text functions. Conversion functions. Date and time functions. Relational functions

DAX Calculated Columns

Types of Calculations New Columns Naming Columns Concatenating Column Contents Tweaking Text Simple Calculations Math Operators Rounding Values Calculating Across Tables Choosing the Correct Table for Linked Calculations Cascading Column Calculations Refreshing Data Using Functions in New Columns Safe Division Counting Reference Elements Statistical Functions Applying a Specific Format to a Calculation Simple Logic: the IF( ) Function Exception Indicators Flagging Data Nested IF() Functions Creating Custom Groups Using Multiple Nested IF() Statements Multiline Formulas Complex Logic Formatting Logical Results

DAX Measures

Basic Aggregations in Measures Using Multiple Measures Cross-Table Measures More Advanced Aggregations Filter Context Row Context Query Context Filter Context Filtering Data in Measures Simple Filters Text Filters Numeric Filters More Complex Filters Multiple Criteria in Filters Using Multiple Filters Calculating Percentages of Totals A Simple Percentage Removing Multiple Filter Elements Visual Totals The ALLEXCEPT() Function Filtering on Measures Displaying Rank Calculation Options

Time Intelligence

Simple Date Calculations Date and Time Formatting Calculating the Age of Cars Sold Calculating the Difference Between Two Dates Adding Time Intelligence to a Data Model Creating and Applying a Date Table Creating the Date Table Adding Sort By Columns to the Date Table Date Table Techniques Adding the Date Table to the Data Model Applying Time Intelligence YearToDate, QuarterToDate, and MonthToDate Calculations Analyze Data As a Ratio over Time Comparing a Metric with the Result from a Range of Dates Comparisons with Previous Time Periods Calculating Sales for the Previous Year Comparison with a Parallel Period in Time Comparing Data from Previous Years Comparing with the Same Date Period from a Different Quarter, Month, or Year Rolling Aggregations over a Period of Time