Introduction to DAX Formulas & Measures for Power Pivot
Last week, you saw an Introduction to Power Pivot for Excel. Today, lets talk about DAX formulas & measures for Power Pivot.
What is a Measure?
A measure is a formula for the values area of Power Pivot table.
A measure can be implicit or explicit.
Implicit measures are created automatically when you drag and drop a field in to Power Pivot values area. For example, in last week’s introduction, we created an implicit measure for SUM of Sales by dragging and dropping the sales amount field in to values area of our power pivot table.
Explicit measures are created by you using New measure button in Power Pivot tab (or Calculated Field button in Excel 2013 Power Pivot tab). You can also create a measure in the Power Pivot window.
Measures vs. Calculated Fields
They both refer to the same thing in the context of Power Pivot. Up to Excel 2010 Power Pivot versions, Microsoft used Measure as the official term.
Starting Excel 2013, Measures became Calculated Fields.
So what is DAX then?
DAX stands for Data Analysis Expression. It is a special language we use to create measures in Power Pivot. Although it is a special language, it looks exactly like our regular Excel formulas. That means you can easily learn the DAX basics and create measures in no time.
Think of DAX as Excel Formula ++. An upgraded version of Excel formulas that can handle power pivot data and give you the calculations you want.
Lets create a measure
Step 1: Decide what the measure should do
The first step is to figure out the need for a measure. Lets say we want a measure to sum up total sales.
Step 2: Launch New Measure screen
In Excel 2010: Power Pivot Ribbon > New Measure
In Excel 2013: Power Pivot Ribbon > Calculation Field > New Calculation Field
This will open below screen (screenshot for Excel 2013 shown below. Excel 2010 looks almost similar).
Step 3: Give our measure a name
Lets call it Total Amount
Step 4: Write the DAX formula
The formula for summing sales is =SUM(sales[sale amount])
This formula looks exactly like an Excel formula!!!
As I mentioned before, the syntax, look & fell of DAX is just like Excel formulas. It is DAX’s power, flexibility & variety that outsmarts Excel formulas.
When you press OK, a new measure (Total Amount) will be created and attached to the Sales table in your power pivot data model. It looks like this,
Step 5: Add this measure to your Power Pivot report
Just drag and drop this measure in to values area of your pivot report. Instantly total sales amount will be calculated based on your report set up.
Why bother creating a measure for such simple thing as SUM?
I know you would be asking this. It seems like a lot of trouble to create a measure, to just show the sum of sales amount. Well, SUM & COUNT are just tip of the DAX iceberg. The power of DAX formula engine is truly phenomenal. To prove it, lets play a small game.
Imagine how much time you would take to write a regular Excel formula or setting up a regular Pivot report to answer each of these questions:
- Count of distinct customers by region & Product category for month of May 2012
- Sum of sales made in weekends only by customer gender & product size for Q1, 2012
- Percentage of sales made in weekends (compared to total sales) by customer name
- Number of customers during lunch hours (between 12noon & 1:30 PM) by week day
Now, how would you feel if I tell you that using DAX, it will take less than 5 minutes to answer all these questions. 5 minutes!!!
And the beauty is, once we have a measure that tells us sales made in weekends, we can use it in any report:
- Sales in weekends by gender & product size – YES
- Sales in weekends by product category & month – YES
- Sales in weekends by year & season – YES
All without any additional work!
Oh this is so tempting, teach me how, teach me now
Don’t worry. I am not going to leave you high & dry. I made a video (30 mins) explaining below topics:
- Introduction to DAX, measures & calculated fields
- Implicit vs. Explicit measures
- Creating a simple DAX measure
- Example DAX measures & explanation:
- Sum of sales
- Count of distinct customers
- Sales made in weekend
- Percentage of sales made in weekend
How can I learn more?
If all this sounds interesting, you would enjoy our upcoming online course on Power Pivot. If you want to know more about our class, please enter your name & email below. I will also send you 2 videos on Power Pivot.
Do you DAX? Share your tips & experiences
It took me a while to wrap my head around the way DAX formulas work. I am still learning and struggling to come up with measures for every thing. But I find them very powerful & addictive. I am now able to create powerful analysis reports & dashboards for my clients, thanks to DAX formulas and Power Pivot.
What about you? Do you play with DAX often? What is your experience like? Please share your ideas, tips & suggestions in comments.
|Can you calculate vacation days in a period? [Homework]||Details about our Power Pivot Course [and a video for those of you not interested]|