<< VIEW FULL COURSE LIST

Microsoft 3.0 - Power BI

Business Solutions with Data Analysis Expressions

COURSE DESCRIPTION

This one-day course provides a fundamental understanding of Data Analysis Expressions (DAX), an essential tool for anyone using Power BI (and SSAS Tabular, Excel Power Pivot and Excel Power Query). Through hands-on application of exercises in Power BI, students will learn basic functions and how to construct formulas and queries so they can start solving real-world BI problems right away. This course is offered privately.

A fully interactive experience, our online classroom offers a unique opportunity to learn from and work with a subject matter expert. Participants get practice performing the skills and can ask questions of the instructor in real time.

Class Length

One Day

CLASS MATERIALS

Students receive a comprehensive set of learning materials, including all course notes and class examples.

Suggested Attendees

Business analysts who want to learn the fundamentals of data acquisition, modeling, visualization and dashboarding with Power BI. No previous experience with is required.

Module 1:  DAX Introduction – Overview and Mechanics

In this introductory module we discuss the similarities and differences of DAX to Excel functions. We will explore the basics for working with DAX formulae and functions as well as the environment within which we construct them. We introduce the critical concept of filter context together with the topics of operator precedence and data types.

We introduce calculated columns and measures, highlighting differences and best practices of each. We look at a couple of commonly used functions when constructing DAX formulae: the CALCULATE() and RELATED() functions.

Module 2:  Text Functions

In this module we focus on text (string) manipulation, exploring a set of functions that work mostly the same as their Excel equivalents. We will explore examples of commonly occurring text string applications (e.g. calculated columns) as well as exceptions when the text value can be converted to a number.

DAX functions covered in this module

  •  CONCATENATE()
  • LEFT()
  •  REPT()
  •  UPPER()
  •  EXACT()
  •  LEN()
  •  RIGHT()
  •  VALUE()
  •  FIND()
  •  LOWER()
  •  SEARCH()

 

  •  FIXED()
  •  MID()
  •  SUBSTITUTE()

 

  •  FORMAT()
  •  REPLACE()
  •  TRIM()

 


Module 3:  Fundamental Functions

Beginning with the powerful filter functions, we get hands on with DAX fundamental functions and see them applied in examples. Based on considerations we have covered in Module 1; we will work with examples within both calculated columns and measures.

We will also examine logical functions, which test various aspects of the data in our model so that we can perform operations on/with that data. Finally, we will explore uncategorized functions, many are known as information functions.

DAX functions covered in this module

  •   ALL()
  •   EARLIER()
  •   ISLOGICAL()
  •   TRUE()
  •   ALLEXCEPT()
  •   EARLIEST()
  •   ISNONTEXT()
  • VALUES()
  •   ALLNOBLANKROW()
  •   FALSE()
  •   ISNUMBER()

 

  •   AND()
  •   FILTER()
  •   ISTEXT()

 

  •   BLANK()
  •   IF()
  •   NOT()

 

  •   CALCULATE()
  •   IFERROR()
  •   OR()

 

  •   CALCULATETABLE()
  •   ISBLANK()
  •   RELATED()

 

  •   DISTINCT()
  •   ISERROR()
  •   RELATEDTABLE()

 


Module 4:  Aggregate Functions – Statistical and More

This module focuses on aggregate functions, including the SUM() and SUMX() math, trig and statistical functions. We will cover several simple aggregation functions (example:  AVERAGE() ) with which most basic Excel users will be familiar with as well as their X-function counterparts ( AVERAGEX() ). We also look at their A-function counterparts. We will discuss differences between the basic and iterator functions and learn about their operation through examples. 

DAX functions covered in this module

  •  AVERAGE()
  •  COUNTA()
  •  MAXA()
  •  SUMX()
  •  AVERAGEX()
  •  COUNTAX()
  •  MIN()

 

  •  AVERAGEA()
  •  COUNTBLANK()
  •  MINX()

 

  •  COUNT()
  •  COUNTROWS()
  •  MINA()

 

  •  COUNTX()
  •  MAX()
  •  SUM()

 


Module 5:  Math and Trig Functions

While trig functions are yet to be added to DAX, there are many useful and popular math functions that enable users to enforce various rounding rules. We will examine the SUM() and SUMX() functions to complete the set of math functions.

DAX functions covered in this module

  • CONCATENATE()
  • LEFT()
  •  REPT()
  •  UPPER()
  • EXACT()
  • LEN()
  •  RIGHT()
  •  VALUE()
  • FIND()
  • LOWER()
  •  SEARCH()

 

  • FIXED()
  • MID()
  •  SUBSTITUTE()

 

  • FORMAT()
  • REPLACE()
  •  TRIM()

 

 


Module 6:  Date & Time Functions

We will introduce the basic date and time functions as well as the time intelligence functions. We will cover similarities between the two functions and the considerations and planning before applying them.

DAX functions (Date & Time) covered in this module

  •   DATE()
  •   HOUR()
  •   TIME()
  •   YEAR()
  •   DATEVALUE()
  •   MINUTE()
  •   TIMEVALUE()
  • YEARFRAC()
  •   DAY()
  •   MONTH()
  •   TODAY()

 

  •   EDATE()
  •   NOW()
  •   WEEKDAY()

 

  •   EOMONTH()
  •  SECOND()
  •   WEEKNUM()

 

DAX functions (Time Intelligence) covered in this module

  •  CLOSINGBALANCEMONTH()
  •  FIRSTDATE()
  •  PREVIOUSDAY()
  •  CLOSINGBALANCEQUARTER()
  •  FIRSTNONBLANK()
  •  PREVIOUSMONTH()
  •  CLOSINGBALANCEYEAR()
  •  LASTDATE()
  •  PREVIOUSQUARTER()
  •  DATEADD()
  •  LASTNONBLANK()
  •  PREVIOUSYEAR()
  •  DATESBETWEEN()
  •  NEXTDAY()
  •  SAMEPERIODLASTYEAR()
  •  DATESINPERIOD()
  •  NEXTMONTH()
  •  STARTOFMONTH()
  •  DATESMTD()
  •  NEXTQUARTER()
  •  STARTOFQUARTER()
  •  DATESQTD()
  •  NEXTYEAR()
  •  STARTOFYEAR()
  •  DATESYTD()
  •  OPENINGBALANCEMONTH()
  •  TOTALMTD()
  •  ENDOFMONTH()
  •  OPENINGBALANCEQUARTER()
  •  TOTALQTD()
  •  ENDOFQUARTER()
  •  OPENINGBALANCEYEAR()
  •  TOTALYTD()
  •  ENDOFYEAR()
  •  PARALLELPERIOD()
 


Module 7:  Solutions for Common Business Challenges

In the final module, we will demo the implementation of DAX in Power BI to provide solutions for common business challenges. We will explore real-life challenges working with dates and where we come across less-than-ideal conditions with data that is already in place. We will also examine approaches to classic BI needs including requirements for running totals, moving averages dealing with the suppression of totals for non-additive numbers, managing the presentation of semi-additive numbers and more.

REGISTRATION DETAILS

  • Senturus accepts payment by credit card, purchase order and invoice.
  • Paying party will receive a payment confirmation email upon payment receipt.
  • Each registered student will receive an email with the course confirmation and details on how to access the online classroom.
  • Students will receive electronic copies of the course materials and may print one hard copy for their personal use. The course materials are copyrighted and any other reproduction or distribution of these materials is strictly prohibited.

We recommend that all attendees have two displays (monitors) so they can see the training presentation and their hands-on class work simultaneously.

Audio for attendees is via VoIP and a PC Headset is highly recommended. Audio is also available via teleconference using a TOLL number.

Cancellations made ten or more calendar days prior to the first day of the course receive a 100% refund.

Cancellations made between five and nine calendar days prior to the first day of the course, receive a 50% refund.

Registrants will be charged 100% of the enrollment fee if a cancellation request is not received at least five calendar days prior to the first day of the course.