Goals of the training

The aim of the training is to familiarize participants with the functions and complex expressions of DAX (Data Analysis Expressions), which can be used for data analysis in Power BI, SQL Server Analysis Services and in the PowerPivot add-in for MS Excel.

During the training, participants will learn to use tables functions, construct messages, prepare rankings and use DAX language for time analysis in a more complex way, that gives the expected results. They will learn how to create tree hierarchies, as well as learn how to handle relationships using functions.

Skills 

Thanks to the training, the participant will be able to:

• define measures, columns, and calculated tables
• create simple and complex DAX formulas
• take care of code optimization
• use information functions, as well as dates and ranking functions
• use functions to manipulate tables
• handle relationships using functions
• create tree hierarchies

Profile of participants

The training is intended for people using the Power BI tool or the PowerPivot add-in for MS Excel, who perform data analysis and create reports based on data from other systems, databases, sheets.   

Prerequisites

The training participants are required to have knowledge of the Windows environment, basic knowledge of Power BI or the Excel Power Pivot add-in (importing data, creating relationships, hierarchies and creating basic measures and calculated columns). Earlier participation in the trainings Power BI - data analysis and visualization or Microsoft Excel - data analysis using Power Pivot, Power View, Power Query add-ons may be useful.

Duration:

• 2 days, 8 teaching hours each

Detailed training program

The essence of the data model

Contexts of performance

Calculated column vs measure 

Data types

Operators

Selected functions review:

• aggregators and iterators
• VALUES and DISTINCT
• RELATED and RELATEDTABLE
• CALCULATE and CALCULATETABLE
• filters and modifiers

Displaying messages - use of information functions

Selected  time analysis functions

Rankings

• using the RANKX function
• preparation of the "top N" analysis

Complex DAX expressions

• syntax
• code optimization
• use in calculations and analyzes

Tables functions

• defining calculated tables
• grouping of data

Advanced relationship handling using the DAX functions

• using inactive relationships
• virtual relationships
• multi-column relationships

Defining the tree hierarchy

DOWNLOAD FILE

• Power BI - Data analysis and visualization 
• Power BI, Power Pivot, Power Query – Analysis of data from spreadsheets and unstructured files
• Power BI i DAX - Analysis based on dates 
• Power BI i DAX - Advanced analysis based on dates
• DAX – Creating advanced formulas for Power BI,  SQL Server Analysis Services and PowerPivot in Excel