50 Useful DAX Functions in Power BI

By /

Data Analysis Expressions (DAX) is a powerful formula language used in Power BI to create custom calculations and aggregations. Below is a comprehensive list of 50 useful DAX functions, categorized by their primary use case, with brief descriptions and examples to help you leverage them effectively in Power BI.

DAX Functions in Power BI

Aggregation Functions

These functions perform calculations on a set of values and return a single value.

  1. SUM
    • Adds all numbers in a column.
    • Example: Total Sales = SUM(Sales[Amount])
  2. AVERAGE
    • Calculates the average of numbers in a column.
    • Example: Avg Sales = AVERAGE(Sales[Amount])
  3. MIN
    • Returns the smallest value in a column.
    • Example: Min Price = MIN(Products[Price])
  4. MAX
    • Returns the largest value in a column.
    • Example: Max Price = MAX(Products[Price])
  5. COUNT
    • Counts non-blank values in a column.
    • Example: Total Orders = COUNT(Orders[OrderID])
  6. COUNTA
    • Counts non-blank values in a column, including text.
    • Example: Customer Count = COUNTA(Customers[Name])
  7. COUNTBLANK
    • Counts blank values in a column.
    • Example: Blank Entries = COUNTBLANK(Sales[Amount])
  8. SUMX
    • Iterates over a table and sums the result of an expression.
    • Example: Total Profit = SUMX(Sales, Sales[Revenue] – Sales[Cost])
  9. AVERAGEX
    • Calculates the average of an expression over a table.
    • Example: Avg Profit = AVERAGEX(Sales, Sales[Revenue] – Sales[Cost])
  10. MAXX
    • Returns the maximum value of an expression over a table.
    • Example: Max Profit = MAXX(Sales, Sales[Revenue] – Sales[Cost])

Date and Time Functions

These functions help manipulate and calculate dates and times.

  1. DATE
    • Creates a date from year, month, and day.
    • Example: Custom Date = DATE(2023, 12, 31)
  2. TODAY
    • Returns the current date.
    • Example: Current Date = TODAY()
  3. NOW
    • Returns the current date and time.
    • Example: Current Time = NOW()
  4. DATEDIFF
    • Calculates the difference between two dates in specified units (e.g., days, months).
    • Example: Days Between = DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY)
  5. EOMONTH
    • Returns the last day of the month for a given date.
    • Example: End of Month = EOMONTH(Sales[OrderDate], 0)
  6. YEAR
    • Extracts the year from a date.
    • Example: Order Year = YEAR(Sales[OrderDate])
  7. MONTH
    • Extracts the month from a date.
    • Example: Order Month = MONTH(Sales[OrderDate])
  8. DAY
    • Extracts the day from a date.
    • Example: Order Day = DAY(Sales[OrderDate])
  9. WEEKDAY
    • Returns the day of the week (1-7) for a date.
    • Example: Day of Week = WEEKDAY(Sales[OrderDate], 2)
  10. TOTALYTD
    • Calculates the year-to-date total for an expression.
    • Example: YTD Sales = TOTALYTD(SUM(Sales[Amount]), Sales[OrderDate])

Filter Functions

These functions manipulate and filter data contexts.

  1. FILTER
    • Returns a table filtered by a condition.
    • Example: High Sales = FILTER(Sales, Sales[Amount] > 1000)
  2. ALL
    • Removes filters from a table or column.
    • Example: Total Sales All = CALCULATE(SUM(Sales[Amount]), ALL(Sales))
  3. ALLEXCEPT
    • Removes filters except for specified columns.
    • Example: Sales by Region = CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Region]))
  4. CALCULATE
    • Evaluates an expression in a modified filter context.
    • Example: Filtered Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = “West”)
  5. CALCULATETABLE
    • Returns a table with modified filter context.
    • Example: West Sales Table = CALCULATETABLE(Sales, Sales[Region] = “West”)
  6. KEEPFILTERS
    • Preserves existing filters while applying new ones.
    • Example: Filtered Sales = CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Sales[Category] = “Electronics”))
  7. SELECTEDVALUE
    • Returns the value of a column when only one value is filtered.
    • Example: Selected Region = SELECTEDVALUE(Sales[Region])
  8. VALUES
    • Returns a single-column table of unique values.
    • Example: Unique Regions = VALUES(Sales[Region])
  9. DISTINCT
    • Returns a single-column table of distinct values.
    • Example: Distinct Products = DISTINCT(Products[ProductName])
  10. RELATEDTABLE
    • Returns a related table based on a relationship.
    • Example: Related Sales = RELATEDTABLE(Sales)

Logical Functions

These functions handle conditional logic.

  1. IF
    • Returns one value if a condition is true, another if false.
    • Example: Sales Status = IF(Sales[Amount] > 1000, “High”, “Low”)
  2. SWITCH
    • Evaluates an expression and returns a value based on matching conditions.
    • Example: Region Label = SWITCH(Sales[Region], “West”, “W”, “East”, “E”, “Other”)
  3. AND
    • Checks if two conditions are true.
    • Example: High Value = IF(AND(Sales[Amount] > 1000, Sales[Quantity] > 10), “Yes”, “No”)
  4. OR
    • Checks if at least one condition is true.
    • Example: Special Order = IF(OR(Sales[Amount] > 5000, Sales[Priority] = “High”), “Special”, “Normal”)
  5. NOT
    • Reverses a logical condition.
    • Example: Non-West Sales = CALCULATE(SUM(Sales[Amount]), NOT Sales[Region] = “West”)

Text Functions

These functions manipulate text data.

  1. CONCATENATE
    • Combines two text strings.
    • Example: Full Name = CONCATENATE(Customers[FirstName], ” ” & Customers[LastName])
  2. LEFT
    • Returns the leftmost characters of a string.
    • Example: First 3 Chars = LEFT(Products[ProductCode], 3)
  3. RIGHT
    • Returns the rightmost characters of a string.
    • Example: Last 3 Chars = RIGHT(Products[ProductCode], 3)
  4. MID
    • Returns a substring from a string.
    • Example: Middle Code = MID(Products[ProductCode], 2, 3)
  5. FIND
    • Returns the starting position of a string within another string.
    • Example: Position of Dash = FIND(“-“, Products[ProductCode])

Mathematical Functions

These functions perform mathematical operations.

  1. ROUND
    • Rounds a number to a specified number of digits.
    • Example: Rounded Sales = ROUND(Sales[Amount], 2)
  2. DIVIDE
    • Performs division and handles divide-by-zero errors.
    • Example: Profit Margin = DIVIDE(Sales[Profit], Sales[Revenue], 0)
  3. ABS
    • Returns the absolute value of a number.
    • Example: Absolute Profit = ABS(Sales[Profit])
  4. SQRT
    • Returns the square root of a number.
    • Example: Root Value = SQRT(Sales[Quantity])
  5. POWER
    • Raises a number to a specified power.
    • Example: Squared Sales = POWER(Sales[Amount], 2)

Relationship Functions

These functions manage relationships between tables.

  1. RELATED
    • Retrieves a value from a related table.
    • Example: Product Name = RELATED(Products[ProductName])
  2. USERELATIONSHIP
    • Activates an inactive relationship for a calculation.
    • Example: Sales by Ship Date = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Statistical Functions

These functions perform statistical analysis.

  1. RANKX
    • Ranks items based on an expression.
    • Example: Sales Rank = RANKX(ALL(Sales), Sales[Amount])
  2. STDEV.P
    • Calculates the standard deviation for an entire population.
    • Example: Sales Std Dev = STDEV.P(Sales[Amount])
  3. VAR.P
    • Calculates the variance for an entire population.
    • Example: Sales Variance = VAR.P(Sales[Amount])

Conclusion

These 50 DAX functions are the backbone of advanced data modeling and analysis in Power BI. By mastering these functions, you can create dynamic reports, find insights, and handle complex business scenarios. Experiment with these functions in your Power BI models to unlock their full potential.

If you want to watch Power BI Basic to Advanced video tutorial, below you have the paly list link.

Power BI Basic to Advanced: Watch Now

Related Posts

Leave a Comment

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

Scroll to Top