DAX Basics in 5 minutes
Our Power BI trainings in Sydney, Melbourne and other cities across Australia cover in detail the concepts required to master Power BI and DAX. This blog post is a quick 5-minutes read to recap the DAX essentials needed for data analysis in Power BI.
We assume that by now, you have a basic idea of what DAX is and why it’s important to learn DAX. But if you’re new to this, here’s a guide to assist your learning about DAX – the expression language of Power BI
DAX involves a lot of data manipulation to bring about the hidden information in raw data. There are two places where we can write DAX:
- Calculated Columns.
- Calculated Measures.
Here’s the one-line difference between calculated column and calculated measure: Calculated measures work on the principle of aggregation reflected by their name “measure” therefore, naked columns cannot be used for creating calculated measures. To understand when to use the calculated columns and when you should use calculated measures
The next important concept in DAX is that of evaluation context. Evaluation context is an “environment” in which the formula is calculated. There are two types of evaluation contexts which are completely different from each other. In one of our blogs, we have discussed the two evaluation contexts used in DAX. The concepts have been discussed with reference to Power BI desktop. To understand evaluation context in detail read What is Evaluation Context in DAX?
By now, it must be clear that calculated Columns create a row context so they can be created using naked column. Whereas, Calculated Measures may or may not create a row context. Row Context is applied to every record of the data table.
The knowledge of evaluation context is incomplete until the concept of filter propagation is properly understood. Filter Context is applied prior as the initial incoming filtering of data. Here’s what filter propagation in Power BI.
Lastly, DAX is a formula language that provides its user with different functions. Functions in DAX perform data manipulation. A function in DAX is a named formula within an expression.
A little bit of everything in DAX:
Let’s see how DAX works in a real world scenario:
1.Right click on the Sales Table in the Fields Pane.
2.Click on New Column.
3. Type the following DAX expression in the formula bar.
Naked columns have been used to create calculated column.
4. Right click on the Sales Table in the Fields Pane.
5. Click on New Measure.
6. Type the following DAX expression in the Formula Bar.
Notice that Naked columns cannot be used to create calculated measure.
7. Encapsulate the naked column in SUM function.
8. Click on Data Tab in the left Ribbon.
Calculated columns have been created, adding additional columns to the table. Whereas Calculated measure is not created. This shows that Measures are memory efficient and do not create row context.
9. Click on Table in the Visualization Pane.
10. Drag and drop the Product Code from the Sales Table to the Values.
11. Drag and drop the Sale Price (Column) from the Sales Table to the Values.
12. Drag and drop the Sale Price (Measure) from the Sales Table to the Values.
The table shows same results for Product Cost (Column) and Product Cost (Measure). This shows that measures have created a row context as per the requirement of visual.
Finally, we study filter context.
13. Click on Slicer in the Visualization Pane.
14. Drag and drop the Sales Person Column to the Field.
15. Click on Adam in the Slicer.
Notice that the Filter Context is applied initially to both the Calculated Column and the Calculated Measure.
Conclusion:
DAX is the formula language of Power BI and Power BI cannot be mastered unless one masters DAX. In this blog post, all the fundamental concepts needed to understand DAX and master DAX are discussed. In the last half of the blog, a practical hands-on exercise covering the concepts in DAX is presented.
Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.
Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses: