The aim of this training course is to enable advanced users of Microsoft Excel and Microsoft Power BI to use the DAX (Data Analysis Expression) language to perform transformations and calculations to adapt their data sets to produce reports and dashboards.
Audience
Data analysis professionals and BI experts who want to deepen their knowledge of Microsoft Excel and Power BI data models with the DAX language to transform data and optimize data models within their organization.
Prerequisites
Completion of at least the Logic and Time Functions course or equivalent for Excel
Adapting the data model with DAX in the Power BI course
Objectives
Modify data models using the DAX language
Learn more about basic DAX functions
Teaching method
Lectures - Demonstrations - Guided and individual exercises
Contents
Chapter 1 - Confirming fundamental concepts DAX
Use the DAX (Data Analysis eXpression) language
Discover the differences between Excel and Power BI
Understanding the DAX context
Review calculated measures and columns
Setting up naming conventions
Using variables
Chapter 2 - Reviewing the main functions
Handling text
Set up logic functions
Using time functions
Exploiting information functions
Creating data tables
Manipulating table structures
Formatting operation blocks
Chapter 3 - Setting up filters and sorting
Setting up a filter
Learn how to cancel filters
Apply calculations with filters
Track existing relationships between tables
Understand how to set up filter tests
Chapter 4 - Counting with DAX
Using counting functions
Counting table rows
Use unique counting functions
Chapter 5 - Using loops
Understanding the use of iterative functions
Building complex iterative functions
Creating measurements with DAX
Handling filters with the CALCULATE function
Working with time functions
Chapter 6 - Optimizing the data model
Review data model performance
Using variables to improve performance
Reduce cardinality
Chapter 7 - Applying best practices
Using error functions
Applying the right references
Define your best practices
Surround yourself with the best
Frédéric Paradis
Certified Trainer and Cloud Architect
As a certified Microsoft trainer, Frédéric describes himself as a Cloud magician who easily navigates the mythical space between technology and reality.
Marc Maisonneuve
Training program director
Marc Maisonneuve has acted as a Training Program Director, professional effectiveness trainer and user tools practice leader at AFI for several years. Mr. Maisonneuve is known for his analytical skills, his legendary calm and his undeniable desire to encourage people to develop their skills. He has the ability to present technological solutions in a natural way and to adapt them to the concrete needs of the workplace.
Vicky Moreau
Trainer
Vicky Moreau is a passionate freelancer and professional in the area of office automation. She holds a college diploma in office automation, most of her solid experience with the Office Suite was acquired while being an autodidact. In fact, she has successfully completed an MOS (Microsoft Office Specialist) Excel certification.
Francis Ferland-Stevenson
Trainer
Francis began as a trainer more than 5 years ago by testing office automation tools designed specifically to met the needs of his colleagues. His calm and his empathy makes him able to adapt his language according to the level of experience of the group. This makes his learnings clear and accessible to anyone. As a trainer, he is therefore attentive to the needs of his students to make sure they meet their objectives and face the challenges.
