Advanced DAX for Microsoft Power BI Desktop
Perform powerful data analysis with DAX for Power BI, SQL Server, and Excel
Description
The purpose of this training is to flatten your learning curve in mastering DAX functions, and use the knowledge to write fast, robust code. The training focuses on examples you can build and run with the free Power BI Desktop and Excel 2016+, and helps you make the most of the powerful syntax of variables (VAR) in Power BI, Excel, or Analysis Services.
Want to leverage all of DAX's remarkable capabilities? This no-compromise "deep dive" course is exactly what you need. Perform powerful data analysis with DAX for Power BI, SQL Server, and Excel
Microsoft Power BI is becoming the world’s most powerful self-service BI platform. This business analytical tool delivers insight into any organization and will give you the ability to drive and direct informed and agile business decisions. Power BI is the easiest to use data analysis, reporting and interactive tool. You can create your own dataset based on a single or multiple sources and transform this data into professional-quality interactive dashboards.
Course Objectives
-
Master core DAX concepts, including calculated columns, measures, and calculation groups
-
Work efficiently with basic and advanced table functions
-
Understand evaluation contexts and the CALCULATE and CALCULATETABLE functions
-
Perform time intelligent calculations analyse performance across different periodicities
-
Use calculation groups and calculation items
-
Use syntax of variables (VAR) to write more readable and maintainable functions
-
Express diverse relationships with DAX, including many-to-many relationships and bidirectional filters
-
Master advanced optimization techniques, and improve performance in aggregations
-
Optimize data models to achieve better compression
-
Measure DAX query performance with DAX Studio and learn how to optimize your DAX
How will this Training Course be Presented?
Live-Online
Prerequisites
-
Microsoft Power BI Desktop downloaded (free download) on a Windows computer or Excel 2016 +
-
You will need to know the basics of working with data in Excel
-
Basic understanding of data analysis is a plus but not required
-
No prior knowledge of Power BI required
Who is this Training Course for?
-
Anyone looking to pursue a career in data analysis or business intelligence
-
Data analysts and Excel users hoping to develop advanced data modelling, dashboard design and business intelligence skills
-
People who want to understand how to create customized visuals
-
All Excel users who work with data, reports and dashboards
-
Management Accountants who are required to report on transactional data sets
-
Marketing and Operational Analysts
-
Entrepreneurs / CEO /Accountants looking to master the Data Analysis process
-
Anyone wanting to transition into Business Analytics
The Course Content
Introducing DAX for Excel and Power BI
-
Understanding DAX calculations
-
Understanding calculated columns and measures
-
Introducing variables
-
Formatting DAX code
Working with DAX Functions
-
Aggregation functions
-
Logical functions
-
Information functions
-
Mathematical functions
-
Trigonometric functions
-
Text functions
-
Conversion functions
-
Date and time functions
-
Relational functions
-
Table functions
Evaluation Context
-
Understanding filter contexts
-
Understanding the row context
-
Nested row contexts on different tables
-
Nested row contexts on the same table
-
Row contexts and relationships
-
Filter context and relationships
Understanding CALCULATE and CALCULATETABLE
-
Creating filter contexts
-
Filtering a single column
-
Filtering with complex conditions
-
Evaluation order in CALCULATE
-
Understanding context transition calculated columns and measures
-
Working with iterators and with CALCULATE
-
Computing averages and moving averages
Time intelligence calculations
-
Using year-to-date, quarter-to-date, and month-to-date
-
Computing time periods from prior periods
-
Mixing time intelligence functions
-
Computing a difference over previous periods
-
Computing a moving annual total
-
Using the right call order for nested time intelligence functions
-
Working with opening and closing balances
-
Advanced time intelligence calculations
-
Using drill-through with time intelligence
-
Working with custom financial year calendars
Variables
-
Introducing VAR syntax
-
The scope of variables
-
Using table variables
-
Common patterns using variables
Working with tables
-
Using CALCULATETABLE
-
Manipulating tables
-
Using tables as filters
-
Implementing OR conditions
-
Narrowing sales computation to the first year’s customers
-
Computing new customers
Advanced DAX calculations in business applications
-
Computing the working days between two dates
-
Computing budget and actual variance analysis
-
Analysing performance against a target
-
Computing same-store sales
-
Computing previous year sales up to last date of sales activity