Data Analysis Expressions(DAX) in Power BI

DAX is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.

It is a syntax language that comprises formulae and expressions that are used in data manipulation. Functions, constants, and operators are used in DAX to create expressions. It is used for creating calculated columns, measures, and custom tables.

We can use different calculation techniques and functions to create measures or calculated columns.

DAX can be used to do useful calculations for data analysis and derive new information from already existing data.

DAX has three fundamental concepts: Syntax, Context, and Functions.

Syntax is the component of the formula we are making. It is the language used in the formula like the command, sign, operators, column or row or table, etc.

Context enables us to do dynamic analysis and helps to change the results of a calculation to reflect the current row or cell selection and any associated data. It can be of two types; Row context and Filter context.

In Row Context we tell DAX which row to use, out of the table, to compute the value. It performs row-by-row operations. For example, When we write an expression in a calculated column, the expression is evaluated for each row of the table, creating a row context for each row.

The Filter-Context is one or more filters applied before that table arrives for use. It can be modified by the functions like CALCULATE ( ) .

Functions are predefined, structured, and ordered formulae. We have to pass arguments into functions for calculations.

Types of Functions in DAX

Aggregate Functions

MIN(ColumnNameOrScalar1, [Scalar2]): Returns the smallest numeric value or smallest string in a column, or the smaller value between two scalar expressions.

MAX(ColumnNameOrScalar1, [Scalar2]): Returns the largest numeric value or largest string in a column, or the larger value between two scalar expressions.

AVERAGE(ColumnName): Returns the average of all the numbers in a column.

SUM(ColumnName): Adds all the numbers in a column.

SUMX(Table, Expression): Returns the sum of an expression evaluated for each row in a table.

TEXT Functions

REPLACE(OldText, StartPosition, NumberOfCharacters, NewText): Replaces part of a text string with a different text string.

SEARCH(FindText, WithinText, [StartPosition], [NotFoundValue]) : Returns the starting position of one text string within another text string. SEARCH is not case-sensitive.

FIND(FindText, WithinText, [StartPosition], [NotFoundValue]): Returns the starting position of one text string within another text string. FIND is case-sensitive.

UPPER(Text): Converts a text string to all uppercase letters.

LOWER(Text): Converts all letters in a text string to lowercase.

FIXED(Number, [Decimals], [NoCommas]): Rounds a number to the specified number of decimals and returns the result as text with optional commas.

CONCATENATE(Text1, Text2): Joins two text strings into one text string.

(If we want to join more than two strings we can use "&" sign.)

LEFT(Text, [NumberOfCharacters]): Returns the specified number of characters from the start of a text string.

RIGHT(Text, [NumberOfCharacters]): Returns the specified number of characters from the end of a text string.

MID(Text, StartPosition, NumberOfCharacters): Returns a string of characters from the middle of a text string, given a starting position and length.

TRIM(Text): Removes all spaces from a text string except for single spaces between words.

Counting Functions

DISTINCTCOUNT(ColumnName): Counts the number of distinct values in a column.

COUNT(ColumnName): Counts the numbers in a column.

COUNTA(ColumnName): Counts the number of values in a column.

COUNTROWS([Table]): Counts the number of rows in a table.

COUNTBLANK(ColumnName): Counts the number of blanks in a column.

Logical Functions

AND(Logical1, Logical2): Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.

OR(Logical1, Logical2): Returns TRUE if any of the arguments are TRUE, and returns FALSE if all arguments are FALSE.

NOT(Logical1): Performs logical NOT (negation) on given expression.

IF(LogicalTest, ResultIfTrue, [ResultIfFalse]): Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

IFERROR(Value, ValueIfError): Returns value_if_error if the first expression is an error and the value of the expression itself otherwise.

DATE Functions

DATE(Year, Month, Day): Returns the specified date in datetime format.

HOUR(Datetime): Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).

WEEKDAY(Date, [ReturnType]): Returns a number from 1 to 7 identifying the day of the week of a date.

EOMONTH(StartDate, Months): Returns the date in datetime format of the last day of the month before or after a specified number of months.

NOW(): Returns the current date and time in Date-Time format.

TODAY(): Returns the current date.

INFORMATION Functions

ISBLANK(Value): Checks whether a value is blank, and returns TRUE or FALSE.

ISNUMBER(Value): Checks whether a value is a number, and returns TRUE or FALSE.

ISTEXT(Value): Checks whether a value is text, and returns TRUE or FALSE.

ISNONTEXT(Value): Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE.

ISERROR(Value): Checks whether a value is an error, and returns TRUE or FALSE.

In Power BI, we can create two primary calculations using DAX − Calculated columns and Calculated measures.

On the modeling tab, we can select the New Column option to create a new column and enter the DAX formula to perform the calculation. We can also rename the column by changing the Column text in the formula bar.

The value of a calculated column is computed during a data refresh and uses the current row as a context.

On the modeling tab, we can select the New Measure option to create a new measure. This will add a new object under the Fields tab with the name Measure. We can write the DAX formula to calculate the value of the new measure, as we did for the newly calculated column. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report.

Measures and calculated columns both use DAX expressions. The difference is the context of evaluation. A measure is evaluated in the context of the cell evaluated in a report or in a DAX query, whereas a calculated column is computed at the row level within the table it belongs to.

Hope this gave some basic idea about DAX in Power BI.

Happy Learning!