Forum Discussion

vissvess's avatar
vissvess
Icon for Helper V rankHelper V
7 years ago
Solved

GCD for multiple values

Hi all,   I recently came accross a requirement to get a calculated column that gets a GCD of multiple values in the related table. Unfortunately, DAX syntax for GCD is for only two values.   My ...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

GCD has the following property:

GCD(GCD(a, b), c) = GCD(a, b, c),

so that means one can calculate it recursively. However, DAX does not support recursive calculations. What remains is the following algorithm.

1. Gather all the numbers for the ID.
2. For each number generate all the divisors by brute force.
3. Intersect the sets of divisors and take the greatest one.

 

However... WHY DON'T YOU DO THIS IN POWER QUERY? Would that not be a lot easier?


Best
Darek

vissvess's avatar
vissvess
Icon for Helper V rankHelper V
7 years ago

Hi Zubair_Muhammad ,

 

Could you please suggest your view.

 

Thanks

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    HI vissvess Anonymous 

     

    Here is a DAX possibility

    Could be very slow.

    But it works with sample data

     

    Pen_Size =
VAR possible_GCDs =
    GENERATESERIES ( 2, MAXX ( RELATEDTABLE ( 'DataTable' ), [Confirmed] ) )
VAR temp =
    ADDCOLUMNS (
        possible_GCDs,
        "Check",
        VAR mycount =
            COUNTROWS ( RELATEDTABLE ( 'DataTable' ) )
        VAR possibleValues =
            CALCULATETABLE ( VALUES ( 'DataTable'[Confirmed] ) )
        RETURN
            SUMX (
                possibleValues,
                IF ( [Confirmed] / [Value] = INT ( [Confirmed] / [Value] ), 0, 1 )
            )
    )
RETURN
    MAXX ( FILTER ( temp, [Check] = 0 ), [Value] )
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        I'd strongly suggest not to do this in DAX unless your model is small. If it's big, this will be not only slow but the compression rate will suffer.

        Best
        Darek
      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Welcome Sir