Dax Function: INT

Category: Mathematical and Trigonometric Functions

The INT function in Power BI is a DAX (Data Analysis Expressions) function used to round down a number to the nearest integer. This function effectively truncates the decimal portion of a number and returns the largest integer less than or equal to the input value.

Purpose

The INT function helps:

1. Convert decimal values into integers for simpler analysis.
2. Prepare data for logical or categorical segmentation.
3. Perform mathematical rounding operations.

Type of Calculations

The INT function performs a mathematical floor operation, truncating any decimal part of a value and retaining only the integer portion. It is commonly used in rounding operations, data grouping, and scenarios where whole numbers are required.

Practical Use Cases

1. Data Simplification: Transform floating-point data into integers for aggregation.
2. Binning: Group data into discrete categories based on integer ranges.
3. Currency Conversion: Simplify financial data by removing decimal fractions.
4. Indexing and Looping: Create integer-based index columns for loops or iterative calculations.

How Does INT Dax Function Works?

The INT function applies the mathematical floor principle:

• If the input is positive, it truncates the decimal portion.
• If the input is negative, it returns the next smaller integer.

Mathematical representation:

 INT(x)=⌊x⌋ 

Where ⌊x⌋ denotes the floor of x.

Examples:

• INT(4.7) → 4
• INT(-2.3) → -3

What Does It Return?

The function returns a scalar integer value, which is the nearest integer less than or equal to the input number.

When Should We Use It?

• Removing Decimals: When exact integers are required for comparison or grouping.
• Data Categorization: Binning numerical data into integer intervals.
• Simplification: Converting floating-point data to whole numbers for aggregation or visualization.

Examples

Basic Usage

Round down a number:

Output:

5

Column Usage

A new column containing the integer values of sales data.

Output: A new calculated column containing the GCD for corresponding rows in “Column1” and “Column2”.

Advanced Usage

Combine with other DAX functions:

Output:

Categorizes values based on whether their integer part is greater than 10.

Tips and Tricks

1. Positive vs. Negative Behavior: Be aware that negative values are rounded down to the next smaller integer.
2. Combine with Modulo: Use with the MOD function for advanced rounding or segmentation.
3. Decimal Elimination: Use the INT function to remove decimals before applying logical or conditional operations.

Potential Pitfalls

• Negative Numbers: The behavior for negative inputs might be counterintuitive; it does not truncate but rounds down further.
• Precision Loss: The decimal part is completely discarded, which may result in loss of precision in calculations.

Performance Impact of INT DAX Function:

• Efficient for scalar operations and small datasets.
• For large datasets, consider pre-aggregating data or applying the INT function only to filtered subsets to optimize performance.

Related Functions You Might Need

• ROUND: Rounds a number to a specified number of digits.
• TRUNC: Truncates a number to a specific number of decimal places.
• MOD: Returns the remainder after division, often used alongside INT for categorization.

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