Power BI —Utilizing DAX

Data Analysis Expressions (DAX) is a formula language used in Microsoft Power BI to solve a number of basic calculation and data analysis problems. DAX does this with a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.

Stated more simply, DAX helps you create new information from data already in your model.

A user needs to have basic knowledge of Power BI Desktop to create a decent report with all the available data. But, if you want to level up and use advanced calculations in your Power BI reports, you need DAX.

Why use DAX?

It’s easy to create a new Power BI file and import some data into it. You can even create insightful reports that show valuable data trends without using any DAX formulas at all. But, what if you need to analyze profit growth percentage across product categories and for different date ranges? Or, you need to calculate month-over-month growth compared to market trends? DAX formulas provide this capability and many other important capabilities as well.

Learning how to create effective DAX formulas will help you get the most out of your data. This is primarily done through the use of calculated columns, measures and custom tables. A great thing about DAX is that the syntax isvery similar to Excel.

DAX Functions

DAX is also known as function language, where the full code is kept inside a function. In Power BI, you can use different function types to analyze data, and create new columns and measures. It includes functions from different categories such as −

• Aggregate
• Text
• Date
• Logical
• Counting
• Information

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order or structure. Arguments can be other functions, another formula, expression, column references, numbers, text, logical values such as TRUE or FALSE, or constants.

DAX includes the following categories of functions: Date and Time, Time Intelligence, Information, Logical, Mathematical, Statistical, Text, Parent/Child, and Other functions. If you’re familiar with functions in Excel formulas, many of the functions in DAX will appear similar to you;

Aggregate Functions

DAX has a number of aggregate functions.

• MIN
• MAX
• Average
• SUM
• SUMX

Counting Functions

Other counting functions in DAX include −

• DISTINCTCOUNT
• COUNT
• COUNTA
• COUNTROWS
• COUNTBLANK

Logical Functions

Following are the collection of Logical functions −

• AND
• OR
• NOT
• IF
• IFERROR

TEXT Functions

• REPLACE
• SEARCH
• UPPER
• FIXED
• CONCATENATE

DATE Functions

• DATE
• HOUR
• WEEKDAY
• NOW
• EOMONTH

INFORMATION Functions

• ISBLANK
• ISNUMBER
• ISTEXT
• ISNONTEXT
• ISERROR

DAX Calculation Types

In Power BI, you can create two primary calculations using DAX −

• Calculated columns: The calculated columns create a new column in your existing table. The only difference between a regular column and a calculated column is that it is necessary to have at least one function in the calculated column. These are used when you want to create a column with filtered or sorted information.
• Calculated measures: creates a field having aggregated values such a sum, ratios, percentages, averages, etc.

Remember: DAX expressions are evaluated from the innermost function going to the outermost one at the last. This makes formulating of a DAX formula important.

DAX functions are unique in the following ways:

• A DAX function always references a complete column or a table.
• If you need to customize calculations on a row-by-row basis, DAX provides functions that let you use the current row value or a related value as a kind of argument, to perform calculations that vary by context.
• DAX includes many functions that return a table rather than a value. For example, you can retrieve a table and then count the distinct values in it, or calculate dynamic sums across filtered tables or columns.
• DAX includes a variety of time intelligence functions. These functions let you define or select date ranges, and perform dynamic calculations based on them.
• Excel has a popular function, VLOOKUP. DAX functions don’t take a cell or cell range as a reference like VLOOKUP does in Excel. DAX functions take a column or a table as a reference.
• Looking up values in another table is easy, and in most cases you don’t need to create any formulas at all.

As you can see, functions in DAX can help you create powerful formulas. DAX formulas are very useful in BI tools like Microsoft Power BI as they help data analysts to use the data sets they have to the fullest potential. We really only touched on the basics of functions. As your DAX skills grow, you’ll create formulas by using many different functions.