DAX is an important concept in Power BI. You cannot master Power BI unless you master DAX. If you’re a beginner in DAX and wondering where to start from then here’s a good news for you, we’re staring a series of DAX Weekends to strengthen your Power BI knowledge. Power BI and DAX go hand in hand. DAX is particularly useful in Power BI Desktop based analysis.
DAX Stands for Data Analysis Expressions and it is the native query and formula language for Microsoft Power Pivot. DAX helps you to customize the analysis in Power BI according to your requirements.
DAX is a formula language and NOT a programming language. This means that formulas are used to define expressions in DAX. A combination of formulas can be used to define custom calculations in Power Pivot in Excel and Power BI.
Lets have a look at some Key Points:
- DAX is a formula language in which the complete code consists of a formula.
- DAX expression may contain conditional statements, value references and nested structures.
- DAX expressions are evaluated starting from the innermost function to the outer most function.
DAX expressions are typed in the Formula bar:
Let’s see what a simple DAX expression looks like:
- This is the name of the expression that you are going to create.
- Equal sign represents the beginning of DAX expression.
- Formula represents the calculation that you want to perform.
- Name of Table.
- Name of Column.
Now let’s map this to a simple example where we calculate the sum of Sales Amount from the sales table using the SUM function.
Where as, In DAX Column references are used to define calculations.
DAX is used to define Calculated Columns as well as Calculated Measures. DAX expressions act as cherry on the top of the cake for a data model. DAX expressions can be used to manipulate data and create columns and measures as per the business question at hand. However, understanding the working of DAX functions is necessary to ensure that the results are accurate.
Due to its parent tool (Microsoft Power BI), M language can be mistaken as DAX. Microsoft Power BI supports different languages, each destined for a particular purpose.
M language is a Query language to Shape and Transform Data. It is Used for Query-Time Transformations. M is mashup language that works on multiple data sources therefore uses commands to shape and transform the data.
DAX on the other hand is a Functional language to build calculations and model data. It is Used for In-Memory Transformations. DAX is a formula language therefore it uses formulas to develop data attributes.
DAX is the native language of the Microsoft query tools. It is a formula language consisting of formulas, expressions, conditional statements, nested statements and referenced values. DAX language follows a certain set of rules. The first rule is related to the order of evaluation. DAX evaluation starts from the innermost formula and eventually, the calculations for the outer most formula are performed at the end. Learning DAX is important to master Power BI and BI solutions.