Beginner Level Guide to DAX

Data Analysis Expressions, commonly known as DAX, is the formula language that drives

Power BI. With DAX, you can: 

• Add calculated columns and measures to your model, using intuitive syntax. 
• Go beyond the capabilities of traditional “grid-style” formulas, with powerful and flexible functions built specifically to work with relational data models

Measures: 

Evaluated in the context of the cell evaluated in a report  or in a DAX query

Some of the points related to Measures:

• Represents a single value per data model
• Computed at run time
• Dynamic results, based on filters
• Filter Context
• Not attached to any specific table

Example: TotalQuantity := SUM(Sales[Quantity])

Calculated Column : 

Computed at the row level within the table it belongs to

Some of the points related to Calculated Column:

• Represents a single value per row
• Computed at compile time
• Dynamic Results, based on Rows
• Row Context

Example: Tenure_Months := Churn[Tenure]*12

Implicit Measures

If we use a calculated column as a value/result, it creates an implicit measure.

For example:

If we have columns such as:

• Tenure in years, 
• Monthly average usage

Goal: to create the overall average usage for that customer

Churn[Tenure_Months] = Churn[Tenure]*12

Total usage would be: 

Churn[Total Usage] = Churn[Tenure_Months] * Churn[Monthly_Average_Usage]

Change in the Primitive Column, i.e. Tenure, will impact the change in the Total Usage column.

DAX is great in two things that are aggregating and filtering. Aggregating means combining group of values into single value.

Examples: Sum, Average, Min, Max, Distinct Count

Syntax of DAX measure is shown in figure below

Before starting with DAX lets revise some of the operators

Important Operators:

Arithmetic Operators:

Comparison operators

Logical Operators

So let’s start with DAX measures

Basic Date & Tine Functions

1] DAY/MONTH/YEAR(): It returns the day of the month (1-31), month of the year (1-12) or year of the given date

Syntax: Name = DAY/MONTH/YEAR(Date)

2] HOUR/MINUTE/SECOND():Returns the hour (0-23), minute (0 59), or second (0 59) of a given datetime value.

Syntax: Name = HOUR/MINUTE/SECOND(DateTime)

3] TODAY/NOW() : Returns the current date or exact time

Syntax: Name = TODAY/NOW()

4] WEEKDAY/WEEKNUM() : Returns a weekday number from 1 (Sunday) to 7 (Saturday), or the week # of the year

Syntax: Name of measure/column = WEEKDAY/WEEKNUM(Date, [Return Type])

5] EOMONTH():Returns the date of the last day of the month, +/-a specified number of months.

Syntax: Name of measure/Column = EOMONTH(StartDate, Months)

6] DATEDIFF():Returns the difference between two dates, based on a selected interval.

Syntax: Name of measure/Column = DATEDIFF(Date1, Date2, Interval)

Basic Logical Operators

1] IF(): Checks if a given condition is met, and returns one value if the condition is TRUE, and another if the condition is FALSE.

Syntax: Name of measure/Column = IF(LogicalTest, ResultIfTrue, [ResultIfFalse])

2] IFERROR(): Evaluates an expression and returns a specified value if the expression returns an error, otherwise returns the expression itself.

Syntax: Name of measure/Column = IFERROR(Value, ValueIfError)

3] AND(): Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE, otherwise returns FALSE.

Syntax: Name of measure/Column = AND(Logical1, Logical2)

4] OR(): Checks whether one of the arguments is TRUE to return TRUE, and returns FALSE if both arguments are FALSE.

Syntax: Name of measure/Column = OR(Logical1, Logical2)

Note: Use the && and || operators if you want to include more than two conditions.

Text Functions

1] LEN():Returns the number of characters in a string

Syntax: Name of measure/Column = LEN(Text)

2] CONCATENATE(): Joins two text strings into one

Syntax: Name of measure/Column = CONCATENATE(Text1, Text2)

3] UPPER/LOWER/PROPER() : Converts letters in a string to upper/lower/proper case

Syntax: Name of measure/Column = UPPER/LOWER/PROPER(text)

4] SUBSTITUTE(): Replaces an instance of existing text with new text in a string.

Syntax: Name of measure/Column = SUBSTITUTE(Text, OldText, NewText, [Instance Number])

Related Function

RELATED() : Returns related values in each row of a table based on relationships with other tables.

Syntax: Name of measure/Column = RELATED (ColumnName)

RELATEDworks almost exactly like a VLOOKUPfunction –it uses the relationship between tables (defined by primary and foreign keys) to pull values from one table into a new column of another

Basic Math and Stat Functions:

1] SUM():Evaluates the sum of a column

Syntax: Name of measure/Column = SUM(ColumnName)

2] AVERAGE() : Returns the average (arithmetic mean) of all the numbers in a column

Syntax: Name of measure/Column = AVERAGE(ColumnName)

3] MAX() : Returns the largest value in a column or between two scalar expressions

Syntax: Name of measure/Column = MAX(ColumnName) 

4] MIN() : Returns the smallest value in a column or between two scalar expressions

Syntax: Name of measure/Column = MIN(ColumnName)

5] DIVIDE() : Performs division and returns the alternate result (or blank) if div/0

Syntax: Name of measure/Column = DIVIDE(Numerator, Denominator, [AlternateResult])

Count Functions

1] COUNT() : Counts the number of cells in a column that contain numbers.

Syntax: Name of measure/Column = COUNT(ColumnName)

2] COUNTA() : Counts the number of non-empty cells in a column (numerical and non-numerical).

Syntax: Name of measure/Column = COUNTA(ColumnName)

3] DISTINCTCOUNT() : Counts the number of distinct or unique values in a column.

Syntax: Name of measure/Column = DISTINCTCOUNT(ColumnName)

CALCULATE 

CALCULATE():Evaluates a given expression or formula under a set of defined filters

Syntax:

Name of the column/measure = CALCULATE(Expression, [Filter1], [Filter2],..)

CALCULATE works just like SUMIFor COUNTIFin Excel, except it can evaluate measures based on ANY sort of calculation (not just a sum, count, etc); it may help to think of it like “CALCULATEIF”

ALL

ALL() : Returns all rows in a table, or all values in a column, ignoring any filters that have been applied.

Syntax:

Name of column/measure = ALL(Table or ColumnName, [ColumnName1], [ColumnName2],….)

Instead of adding filter context, ALL removesit. This is often used when you need unfiltered values that won’t react to changes in filter context (i.e. %of Total, where the denominator needs to remain fixed)

FILTER

FILTER():Returns a table that represents a subset of another table or expression.

Syntax:

Name of column/measure = FILTER(Table, Filter Expression)

FILTER is used to add new filter context, and can handle more complex filter expressionsthan CALCULATE (by referencing measures, for example)

Since FILTER returns an entire table, it’s almost always used as an inputto other functions, like CALCULATE or SUMX

ITERATOR(“X”) FUNCTIONS

Iterator(or “X”) functionsallow you to loop through the same calculation or expression on each row of a table, and then apply some sort of aggregation to the results (SUM, MAX, etc)

Syntax: 

Name of column/Measure = SUMX(Table, Expression)

Iterator can be applied to Aggregating functions.

TIME INTELLIGENCE FUNCTIONS

Time Intelligencefunctions allow you to easily calculate common time comparisons:

1] Performance To Date

Syntax: Name of column/Measure = CALCULATE(Measure, DATESYTD(Calendar[Date]))

2] Previous Period 

Syntax: Name of column/Measure = CALCULATE(Measure, DATEADD(Calendar[Date], -1, MONTH))

3] Running Total 

Syntax: Name of column/Measure = CALCULATE(Measure,

DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -10, DAY))

So these were some of the important DAX functions which you should remember. There are many more but this is the basic and the used ones. I hope you liked the blog.

Thank You!!