Dax Function: GDC

Category: Mathematical and Trigonometric Functions

The GCD function in Power BI is a DAX (Data Analysis Expressions) function used to calculate the greatest common divisor (GCD) of two or more integers. The GCD is the largest positive integer that divides each of the given numbers without leaving a remainder.

Purpose

The GCD function helps:

1. Simplify numerical ratios by finding common divisors.
2. Identify relationships between numbers in datasets.
3. Perform mathematical or logical operations requiring common divisors.

Type of Calculations

The GCD function performs arithmetic calculations to determine the highest shared factor between integers. It is especially useful in number theory and optimization problems.

Practical Use Cases

1. Data Simplification: Simplifying ratios in financial or business data.
2. Optimization: Calculating evenly distributed groups or allocations based on shared factors.
3. Engineering and Scientific Analysis: Finding common intervals or periods in datasets.

How Does GCD Dax Function Works?

The GCD function uses the mathematical principle of Euclid’s Algorithm:

1. If aaa and bbb are integers, the GCD is calculated by repeatedly finding the remainder when aaa is divided by bbb, until the remainder is zero.
2. The divisor at this point is the GCD.

Mathematically:

 GCD( a, b)=GCD(b,a mod  b) 

This process is repeated iteratively for more than two numbers.

What Does It Return?

The function returns a single scalar value, which is the greatest common divisor of the provided integers.

When Should We Use It?

• Ratio Simplification: To find the simplest form of a ratio.
• Group Divisions: Determine optimal group sizes based on shared factors.
• Algorithmic Operations: Useful in implementing logic requiring common divisors.

Examples

Basic Usage

Find the GCD of two numbers:

Output:

6 (The largest divisor common to 48 and 18 is 6.)

Column Usage

Apply the GCD function to two columns:

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

Advanced Usage

Combine GCD with other functions:

Output:

Calculates the GCD of the summed sales and expenses.

Tips and Tricks

1. 1. Validate Inputs: Ensure all inputs are integers. Non-integer or missing values may lead to errors.
   2. Combine with Aggregates: Use in conjunction with SUM, AVERAGE, or other aggregation functions to derive meaningful insights.
   3. Large Datasets: Preprocess or group data to limit the number of calculations on large datasets for better performance.

Performance Impact of GCD DAX Function:

• Efficient for small datasets or scalar values.
• For large datasets, consider limiting the range of integers or optimizing through preprocessing.

Related Functions You Might Need

• LCM: Calculates the least common multiple of numbers.
• MOD: Returns the remainder of division, which is integral to Euclid’s Algorithm.
• ROUNDUP/ROUNDDOWN: Helpful for preparing integers from decimals.

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