Power Pivot
You may be missing out a lot of powerful Excel functionality if you are still not using Power Pivot or “Excel data model”, as it has been described since the release of Excel 2016. By using Power Pivot, you also avoid some of the traditional limitations such as row limits in sheets.
We wanted to post a small entry about this topic and explain in a very quick way, terms that may be new to some of the daily excel users. Power Pivot started as a downloadable add-in for Excel since the 2010 version and was finally included as part of the product since Excel 2016.
Awesome, but what is DAX?
DAX stands for Data Analysis Expressions and it’s the programming language of Power Pivot, Power BI and SQL Server Analysis Services, meaning you can add 1-3 amazing products to your business intelligence toolkit by learning one language.
Dax is not like other programming languages; it is a functional language meaning that is made of function calls, like Excel, you write a formula that receives parameters and you can also call other functions or formulas from within.
In DAX there is no concept of loops, statements and jumps. Is all made of expressions, so this may be the challenging part for those with programming backgrounds.
Who is DAX for?
- Any excel professional/ user who works with power pivot and would like to leverage DAX to implement BI solutions in their workplace, own business etc.
- Power BI users that want to develop further their skills by having a better understanding of DAX formulas and implement them in their models.
- Anyone interested in business intelligence in order to solve common business questions and problems related to day to day work, reports and dashboards.
- Business analyst looking to improve tasks and reports that can be replaced with a data model in excel using Power Pivot and DAX expressions.
Learning DAX
Be mindful that DAX sometimes looks easy, but you’ll have to spend a few hours reading and understanding some new concepts before moving to practice.
Of course, you can quickly start writing formulas in DAX specially with Excel background, as there is a lot of resemblance in many expressions, but without the understanding of the DAX behavior, you may get unexpected results from your calculations.
The Data model
In a few words, a data model is a set of tables, linked by relationships. The simplest form of a data model is a single table.
To begin your journey of learning DAX, we really recommend going through some basic concepts on data models and relationships, this will help you get a solid base for all the power you’ll get from DAX.
Where to Start?
If you are an excel pro user, chances are that you are already working with Power Pivot or at least heard of it, if you haven’t, don’t worry, you can also start taking advantage of the data revolution that Microsoft has brought to everybody.
Aside from some basic data modeling concepts, to get familiar with DAX we recommend starting by reading about:
- Loading tables to the data model.
- The basic DAX syntax.
- Calculated columns.
- Evaluation context.
- Measures.
- Table calculations.
- Iterations.
- Context transitions.
If you want to check out some free material online, we think the intro course from the guys at SQLBI is very good, it is not very long but you’ll get to see a little of what you can accomplish with DAX:
https://www.sqlbi.com/p/introducing-dax-video-course/
They also have a tool to format your expressions, this can help you get accustomed to use best practices when authoring formulas with DAX, just visit the site below, paste your code and format. https://www.daxformatter.com/
Have fun!
