Tutorial on DAX Functions in Power BI
3 mins read

Tutorial on DAX Functions in Power BI

What is DAX?

Data Analysis Expressions (DAX) is a formula expression language. Expression language for
   • Power BI.
   • Power Pivot for Excel.
   • SSAS Tabular Model.
   • Azure Analysis Services.

Why DAX?

Designed to support a Larger User Base Simpler than traditional technical languages to learn. Leverage existing knowledge of Excel formulas. Less of a learning curve for Analysts

DAX Functions

  • Aggregate Function’s
  • Date and Time Functions
  • Time Intelligence Functions|
  • Filter Functions
  • Financial Functions
  • Information Functions
  • Logical Functions
  • Math and Trig Functions
  • Relationship Functions|
  • Statistical Functions
  • Text Functions
  • Table Manipulation Functions
  • Parent and Child Functions
Aggregate Functions
  1. SUM: Calculates the sum of a column or a set of values.

    • Example: SUM(Table[Column])
  2. AVERAGE: Computes the average of a column or a set of values.
    • Example: AVERAGE(Table[Column])
  3. COUNT: Counts the number of rows in a table or column.
    • Example: COUNT(Table) or COUNT(Table[Column])
  4. MIN/MAX: Returns the minimum or maximum value from a column.
    • Example: MIN(Table[Column]) or MAX(Table[Column])
Date and Time Functions

Date and Time functions are useful for working with date and time data in your DAX formulas:

  1. TODAY/NOW: Returns the current date and time.

    • Example: TODAY() or NOW()
  2. DATE/YEAR/MONTH/DAY/HOUR/MINUTE/SECOND: Extracts specific components from a date or time value.

    • Example: YEAR([DateColumn]) or HOUR([TimeColumn])
  3. DATEADD/DATEDIFF: Adds or calculates the difference between two dates.

    • Example: DATEADD([DateColumn], 1, DAY) or DATEDIFF([StartDate], [EndDate], DAY)
Time Intelligence Functions

Time Intelligence functions are designed for time-based calculations, especially when working with date tables:

  1. TOTALYTD: Calculates the total for a measure from the beginning of the year.

    • Example: TOTALYTD(SUM(Sales[Amount]), Date[Date])
  2. SAMEPERIODLASTYEAR: Retrieves the value from the same period in the previous year.

    • Example: SAMEPERIODLASTYEAR(SUM(Sales[Amount]), Date[Date])
Filter Functions

Filter functions allow you to manipulate data based on specific conditions:

  1. FILTER: Filters a table based on specified conditions.

    • Example: FILTER(Sales, Sales[Amount] > 1000)
  2. ALL: Removes filters from columns or tables.

    • Example: ALL(Customer[Country])
Financial Functions

Financial functions are used for calculations related to finance and investments:

  1. NPV/IRR: Calculate the Net Present Value and Internal Rate of Return.
    • Example: NPV(DiscountRate, CashFlows) or IRR(CashFlows)
Information Functions

Information functions provide information about data types:

  1. ISBLANK/ISNUMBER/ISTEXT/ISDATE: Checks if a value is blank, a number, text, or a date.
    • Example: ISBLANK([Column])
Logical Functions

Logical functions perform logical operations in DAX formulas:

  1. IF/AND/OR/NOT: Perform conditional statements and logical operations.
    • Example: IF([Sales] > 1000, "High", "Low")
Math and Trig Functions

Math and Trig functions perform mathematical and trigonometric operations:

  1. ROUND/FLOOR/CEILING: Round numbers to a specified precision.
    • Example: ROUND([Value], 2)
Statistical Functions

Statistical functions are useful for statistical analysis:

  1. AVERAGEX/MAXX/MINX: Calculate averages, maximums, or minimums for an expression.
    • Example: AVERAGEX(Products, [Sales])
Relationship Functions

Relationship functions help you work with relationships between tables:

  1. RELATED/RELATEDTABLE: Access related columns or tables.
    • Example: RELATED(Customer[Name])
Text Functions

Text functions help manipulate and format text data:

  1. CONCATENATE/LEFT/RIGHT/MID: Combine text or extract portions of text.
    • Example: CONCATENATE([First Name], " ", [Last Name])
Parent and Child Functions

Table manipulation functions allow you to modify and manipulate tables:

  1. SUMMARIZE/FILTER/ADDCOLUMNS: Create new tables or modify existing tables.
    • Example: SUMMARIZE(Sales, Sales[Product], Sales[Revenue])
Table Manipulation Functions

Parent and Child functions are used for working with parent-child hierarchies:

  1. PATH/PATHITEM: Retrieve paths and specific elements from parent-child hierarchies.
    • Example: PATH(Customer[Name], [Customer Key])

Leave a Reply

Your email address will not be published. Required fields are marked *