1. DAX in Power Pivot
Hello, and welcome! In this screencast, we will review how to use DAX in calculated columns and measures.
Let’s get started in the Power Pivot window and examine our schedule table. PowerPivot Gym is interested in the difference between the number of people who attend a scheduled class and the maximum allowed. To do this, we simply need to subtract the number of attendees in our schedule table from the max slots in the classes table. How can we do that?
Because we have defined a relationship between these two tables, we can use the RELATED function to bring this column in. So we’ll add a function by right-clicking insert new column, then name this Maximum Attendees, and then we’ll use RELATED to find classes[Max Slots]. Great!
The next step is to performance some basic arithmetic. Remember from your early school days that arithmetic is addition, subtraction, multiplication, and division. These types of calculations are easy in DAX because they don’t require any special functions, and it works just like in Excel.
Let’s create a new column called “Open Slots” to subtract Attended from Maximum Attendees. See how each row gets a calculation? This is row context in action.
We can create measures here, too. See the bottom part of the Power Pivot window. This is where we can add measures for this table. It’s easiest just to keep them all in the left corner, so we don’t lose them. So, let’s create a measure called attendance percentage. To do this we first need to SUM Attended, then we can divide with the slash and SUM the Maximum Attendees.
Cool! Now, let’s format this so it’s easier to look at. Right-click, format. Choose number, then percentage from the dropdown menu, and we’ll keep it at two decimal places.
Great! Now, let’s add this measure to a pivot table with 'Instructor' to see who has the highest attended classes. Notice how the filter context is already at work in this pivot table? The measure is being calculated on the query, and since the table has split each row by 'Instructor ', it filters the measure in this way for each instructor.
Now you’ve seen it, it’s your turn to give it a try.
2. Let's practice!