Power BI: What is DAX?

What is DAX? 

DAX (Data Analysis Expressions) is a formula expression language developed by Microsoft to interact with data in different analysis services and visualisation tools platforms like Power BI, PowerPivot and SSAS tabular models. DAX can seem familiar to competent excel users as the language uses similar formulas and expressions.  

DAX programming formula 

The DAX programming formula contains two data types:  

• Numeric - integers, currency, and decimals 
• Other- string and a binary object. 

The DAX function also has other conditional statements, functions and value references. 

DAX Functions 

In Power BI, you can use different function types to analyse data and create new columns and measures.  

The functions include: 

Text functions 

Text functions manipulate strings. They include: 

• REPLACE 
• SEARCH 
• UPPER 
• FIXED 
• CONCATENATE 

Time Intelligence functions 

They support calculations to compare and aggregate data over periods - supporting days, months, quarters, and years. 

Information functions 

They provide information about the data type or filter context of the argument. The functions include: 

• ISBLANK 
• ISNUMBER 
• ISTEXT 
• ISNONTEXT 
• ISERROR 

Logical functions 

They act upon an expression to return information about the values or sets. The following is the collection of Logical functions: 

• AND 
• OR 
• NOT 
• IF 
• IFERROR 

Math and Trig functions 

These functions are very similar to the Excel mathematical and trigonometric functions. 

Parent-child functions 

These functions help flatten a parent-child relationship in a regular one. 

Aggregation functions 

Expect a scalar value return when you apply an aggregation function to a column or an expression evaluated by iterating a table expression. DAX has several aggregate functions like: 

• MIN 
• MAX 
• Average 
• SUM 
• SUMX 

Date and Time functions 

They help create calculations based on dates and times. They include: 

• DATE 
• HOUR 
• WEEKDAY 
• NOW 
• EOMONTH 

Filter functions 

They manipulate tables and filter contexts. 

Relationships management functions 

These functions manage and control relationships between tables. 

Statistical functions 

They perform Statistical aggregation functions. 

Table manipulation functions 

These functions manipulate and return tables. 

Financial functions 

Financial functions correspond to Excel functions with the same name. 

Other functions 

These are special functions that cannot be classified into other categories. 

DAX Calculations 

DAX formulas are used in measures, calculated columns, calculated tables, and row-level security. Power Bi has an option that opens the formula bar to enter the DAX formula to perform a calculation.  

In Power BI, you can create two calculations using  a calculated column or a measure: 

• Calculated columns - a column that you add to an existing table (in the model designer), and then create a DAX formula that defines the column's values. When a calculated column contains a valid DAX formula, values are calculated for each row as soon as the formula is entered. This means the values are stored in the in-memory data model. This formula would generate value for each row in your data and allow you to summarise it to any level you choose when visualising it. 
• Calculated measures – a dynamic calculation formula where the results change depending on context. Calculated Measures support combining and filtering model data - using multiple attributes such as a Power BI report. Measures are evaluated based on the context of the reporting level you are viewing. The measure will automatically scale with your context as you summarise the data. 

It is also worth noting that DAX calculations are carried out in two contexts: 

• Row context - the rows, or selected rows of a table. 
• Filter context - defined by the filters on a pivot table, or visualisation. 

You must understand the row and filter context when writing DAX calculations because failing to make these considerations may give you an unexpected value or result. 

Importance and benefits of knowing DAX 

DAX is useful in the following ways: 

• It provides you with the ability to manipulate data - without creating a new dataset. DAX allows you to have a single dataset that you enhance with calculations. 
• You can select, join, and dynamically filter data. The dashboard can take input from the users and use it to generate calculated columns, measures, and tables. 
• A DAX code can improve the user experience. With a simple DAX expression, you can create a measure to the formula to create a value of your choice. 
• A well-written expression can speed things up on your dashboard, thereby limiting the usage of resources.  
• DAX will improve your thinking on how to - merge, filter, select and manipulate data - making you a better data professional.