Dax Function: TRUNC

Category: Mathematical and Trigonometric Functions

The TRUNC function in Power BI is a mathematical DAX function that truncates a number to a specified number of decimal places, removing the fractional part beyond the specified precision.

Purpose:

• The TRUNC function is used to return the integral part of a number or limit the number of decimal places without rounding.
• It is useful for preparing data for visualizations, formatting, or performing calculations where precision to a certain decimal place is required.

Type of Calculations:

• The TRUNC function performs truncation, which removes decimal places beyond a specified level, unlike rounding that alters the number based on the value of digits.

Practical Use Cases:

1. Formatting Numeric Data: Display numbers without additional fractional parts for easier reading or compliance with reporting standards.
2. Data Validation: Ensure numeric fields conform to specific decimal precision.
3. Financial Calculations: Prevent rounding errors in monetary values by truncating extra decimals.

How Does TRUNC Dax Function Works?

• The TRUNC function removes all digits after the specified decimal place, without rounding the value:

  1. If num_digits = 0, the function returns the integer portion of the number.
  2. If num_digits > 0, the function retains that many decimal places.
  3. If num_digits < 0, truncation is applied to the left of the decimal point, effectively zeroing out lower-order digits.

  For example:

  • TRUNC(12.345, 2) returns 12.34.
  • TRUNC(12.345, 0) returns 12.
  • TRUNC(12345, -2) returns 12300.

What Does It Return?

The TRUNC function returns a numeric scalar value truncated to the specified number of decimal places. If num_digits is negative, the truncation occurs on the integer part of the number.

When Should We Use It?

• Data Cleaning: Format numeric data to a specific precision for reports.
• Avoid Rounding Errors: Use in financial models or calculations where rounding could introduce inaccuracies.
• Visual Display: Simplify the appearance of numeric data for end-users by truncating unnecessary decimal places.
• Truncate Large Numbers: Reduce a number’s significant digits for aggregation or categorization.

Examples

Basic Usage

Truncate a number to two decimal places:

Output: 12.34.

Column Usage

Apply truncation to a column of data:

Truncates each value in the Table[Values] column to one decimal place.

Advanced Usage

Combine TRUNC with other DAX functions:

Result: A custom floor function that truncates positive numbers and adjusts negative ones.

Tips and Tricks

• Avoid Confusion with ROUND: Use TRUNC when you need exact truncation without any rounding.
• Negative num_digits: Use a negative num_digits to truncate numbers to a specific level in the integer part (e.g., thousands or hundreds).
• Combine with INT: Use with the INT function to manipulate integer values further.

Performance Impact of TRUNC DAX Function:

• The TRUNC function is lightweight and efficient for scalar inputs or small datasets.
• When used on large datasets, apply in calculated columns sparingly to avoid performance bottlenecks.

Related Functions You Might Need

• ROUND: Rounds a number to a specified number of digits.
• INT: Returns the integer portion of a number by truncating the decimal part.
• FIXED: Formats a number as text with a specified number of decimals.

Want to Learn More?
For more information, check out the official Microsoft documentation for TRUNC. You can also experiment with this function in your Power BI reports to explore its capabilities.

If you’re looking to unlock the full potential of Power BI and take your data insights to the next level, our expert Power BI consulting services are here to help. Whether you need assistance with implementing advanced DAX functions like the ones discussed here, creating interactive dashboards, or optimizing your data models for better performance, our team of seasoned Power BI consultants is ready to provide tailored solutions for your business. Visit our Power BI Consulting page to learn more about how we can empower your organization with data-driven decisions.