10 Types of DAX Functions in Power BI

by
User

Types of DAX Functions in Power BI

Data Analysis Expressions (DAX) is a formula language used in Power BI, Excel Power Pivot, and SQL Server Analysis Services (SSAS). DAX functions can be categorized into several types based on their functionality. Here’s an overview of the main types of DAX functions:

  1. Math and Trig Functions:
    • ABS: Returns the absolute value of a number.
    • ROUND: Rounds a number to a specified number of digits.
    • ROUNDUP and ROUNDDOWN: Round a number up or down, respectively.
    • EXP: Returns are raised to the power of a given number.
    • LOG: Returns the logarithm of a number to a specified base.
  2. Statistical Functions:
    • AVERAGE: Returns the average of a set of numbers.
    • MIN and MAX: Return the minimum or maximum value in a set of numbers.
    • COUNT: Counts the number of rows in a table or column.
    • SUMX and AVERAGEX: Iterate over a table and aggregate values based on a formula.
  3. Text Functions:
    • CONCATENATE and CONCATENATEX: Combine text strings.
    • LEFT and RIGHT: Extract characters from the beginning or end of a text string.
    • LEN: Returns the number of characters in a text string.
    • SUBSTITUTE: Replaces occurrences of a specified substring with another substring.
  4. Date and Time Functions:
    • TODAY and NOW: Return the current date or date and time.
    • YEAR, MONTH, and DAY: Extract components of a date.
    • DATEDIFF: Calculate the difference between two dates.
    • EOMONTH: Returns the last day of the month.
  5. Filter Functions:
    • FILTER: Returns a table that has been filtered based on a specified condition.
    • ALL: Returns all the rows in a table, or all the values in a column, ignoring any filters.
    • RELATED: Retrieves a related value from another table.
  6. Information Functions:
    • ISBLANK and IFERROR: Check if a value is blank or handle errors.
    • ISEVEN and ISODD: Check if a number is even or odd.
    • TYPE: Returns the data type of an expression.
  7. Logical Functions:
    • AND, OR, and NOT: Perform logical operations.
    • IF: Returns one value if a condition is true and another if false.
    • SWITCH: Compares an expression against a list of values and returns the corresponding result.
  8. Statistical Functions:
    • SUM, AVERAGE, MIN, MAX, COUNT: Aggregation functions for numerical values.
    • SUMX, AVERAGEX, MINX, MAXX, COUNTX: Iterate over a table and aggregate values based on a formula.
  9. Time Intelligence Functions:
    • TOTALYTD, TOTALQTD, TOTALMTD: Calculate year-to-date, quarter-to-date, and month-to-date totals.
    • SAMEPERIODLASTYEAR: Returns a table that contains a column of dates shifted one year back.
  10. Advanced Statistical Functions:
    • PERCENTILE.INC, PERCENTILE.EXC: Calculate percentiles.
    • QUARTILE.INC, QUARTILE.EXC: Calculate quartiles.
    • RANK.EQ, RANK.AVG: Rank values in a list.

These are just a few examples, and there are many more DAX functions in Power BI. The choice of function depends on the specific requirements of your analysis or calculation. Understanding the different categories of functions and their use cases can help you build more powerful and flexible calculations in Power BI.

Leave a Comment