What is DAX in Power BI

Introduction

In Microsoft Power BI, Data Analysis Expressions (DAX) formula language is used to perform data modeling, calculations, and aggregations. It is used to create calculated columns, measures, and tables to enhance data analysis.

DAX allows us to create formulas with combining functions, operators, and values. Dynamic calculations can be performed on data stored in tables. It can operate within a relational model.

Common DAX Functions

The various DAX functions are categorised as:

Aggregation function
Logical function
Text functions
Mathematical functions

Aggregation Functions

Aggregation functions help to summarize data from a table or column.

SUM – Returns the total sum of a column.

TotalSales = SUM(Sales[Amount])

AVERAGE – Calculates the average of a column.

AvgSales = AVERAGE(Sales[Amount])

COUNT – Returns the count of non-empty values in a column.

OrderCount = COUNT(Sales[OrderID])

DISTINCTCOUNT – Counts the number of unique values in a column.

UniqueCustomers = DISTINCTCOUNT(Sales[CustomerID])

MAX / MIN – Returns the highest or lowest value from a column.

MaxSale = MAX(Sales[Amount])

MinSale = MIN(Sales[Amount])

Logical Functions

Logical functions evaluate various conditions and return results based on logical expressions.

IF – Returns different results based on a condition.

HighSale = IF(Sales[Amount] > 1000, “High”, “Low”)

SWITCH – Works like a case statement, evaluating multiple conditions.

SalesCategory = SWITCH(

TRUE(),

Sales[Amount] > 1000, “High”,

Sales[Amount] > 500, “Medium”,

“Low”

)

Text Functions

Text functions are used to manipulate and format text values.

CONCATENATE – Joins two text strings together.

FullName = CONCATENATE(Customers[FirstName], Customers[LastName])

LEFT, RIGHT, MID – Extracts portions of a text string.

FirstThreeLetters = LEFT(Customers[FirstName], 3)

Mathematical Functions

Mathematical functions are used to perform numerical operations and logarithmic calculations for creating calculated columns and measures in Power BI.

1. Basic Arithmetic Functions

To perform basic mathematical operations.

ABS – Returns the absolute (positive) value of a number.

AbsoluteValue = ABS(-100)

// Result: 100

CEILING – Rounds a number up to the nearest integer.

CeilingValue = CEILING(4.3, 1)

// Result: 5

FLOOR – Rounds a number down to the nearest integer.

FloorValue = FLOOR(4.7, 1)

// Result: 4

ROUND – Rounds a number to a specified number of decimal places.

RoundedValue = ROUND(4.567, 2)

// Result: 4.57

ROUNDDOWN – Rounds a number down towards zero.

RoundDownValue = ROUNDDOWN(4.567, 2)

// Result: 4.56

ROUNDUP – Rounds a number up away from zero.

RoundUpValue = ROUNDUP(4.123, 2)

// Result: 4.13

2. Exponential and Logarithmic Functions

To calculate powers and logarithms.

POWER – Returns the result of a number raised to a power.

PowerValue = POWER(2, 3)

// Result: 8

EXP – Returns e raised to the power of a given number.

ExponentialValue = EXP(1)

// Result: 2.718

LOG – Returns the logarithm of a number in a specified base.

LogValue = LOG(100, 10)

// Result: 2

LN – Returns the natural logarithm of a number.

NaturalLogValue = LN(20)

3. Trigonometric Functions

To calculate angles and trigonometry.

COS – Returns the cosine of a given angle in radians.

CosValue = COS(PI()/3)

SIN – Returns the sine of a given angle in radians.

SinValue = SIN(PI()/2)

TAN – Returns the tangent of a given angle in radians.

TanValue = TAN(PI()/4)

ACOS, ASIN, ATAN – Returns the inverse cosine, sine, and tangent respectively.

ArcCosValue = ACOS(0.5)

ArcSinValue = ASIN(0.5)

ArcTanValue = ATAN(1)

4. Statistical and Rounding Functions

To perform rounding, randomness, and statistical calculations.

PI – Returns the value of π (pi).

PiValue = PI()

// Result: 3.14159265358979

RAND – Returns a random number between 0 and 1.

RandomValue = RAND()

RANDBETWEEN – Returns a random number between two specified values.

RandomBetween = RANDBETWEEN(1, 100)

MOD – Returns the remainder after division.

ModulusValue = MOD(10, 3)

// Result: 1

SIGN – Returns -1 for negative numbers, 1 for positive numbers, and 0 for zero.

SignValue = SIGN(-10)

// Result: -1

Request more information

What is DAX in Power BI