The two languages that we use with Power BI are Data Analysis Expressions, otherwise known as DAX, as well as Power Query M language.
DAX is a formula expression language used across a number of programs such as Power BI, Power Pivot in Excel and Analysis Services. DAX formulas include functions, operators and value to perform calculations and queries on data in related tables and columns inside of the data models.
When we create DAX formulas, we call these Measures. A measure is a dynamic calculation where results change depending on the context associated with it. There are over 250 functions that can be used inside of a measure, which shows just how tough it is to become an expert in DAX. That being said, knowing a few of the most commonly used functions can get you a long way. DAX measures can also be used in combination with each other to easily create further measures. Editing a base measure will see the change flow through all the other measures it is used in. As your DAX skills improve, you will find that you can more easily optimise your report’s performance and better represent your data visually.
Power Query M language is the powerful language that works behind the scenes of Power Query. Any transformations that you apply to your data will be written into the M language of that query. M may look scary to use and read, but its syntax is easy to learn once you get started. Knowing how M works and how to use it can provide huge amount of upside into your reports, as well as being able to push some of the transformation steps back to your data source.
The syntax of M is simple and easy to understand. It has two blocks of programming: ‘let’ expression block and ‘in’ expression block. let is the definition of all variables, in is the output. One thing to be careful about is, that unlike in DAX functions, M is case sensitive, which can cause errors if not applied correctly. Power Query is a step-by-step transformation. Each step creates a new line as part of the let expression block. The new variable references the previous variable, and the transformation steps are carried out in that set order. The latest variable is always shown inside of the in expression block to finalise the transformation process.
