Forum Discussion

vissvess's avatar
vissvess
Helper 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
    Helper V
    7 years ago

    Hi Anonymous ,

     

    This is not that only 3 entries.

     

    There may be n entries.

     

    So could you suggest me a workaround in detail for me to get the GCD of n numbers in column.

     

    Also, if DAX doesnot support recursive calculations, could it be done in M (Power Query)?.

     

    Thanks

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      First of all, the formula I've given you works for ANY number of arguments, not just 3. The extension into n arguments happens through The Induction Principle that you should be familiar with from your mathematics class.

      Second, I'll check if there is a function in Power Query that would do that for you.

      Best
      Darek
      • vissvess's avatar
        vissvess
        Helper V
        7 years ago

        Hi Anonymous ,

         

        It is obvious that the math would work.

         

        I need to clarify my requirement, that there are unknown number of variables to be included.

         

        As far as my search, I have not came accross a M script for GCD directly.

        Yet, there would a workaroud. Please share if you formulate one.

         

        Thanks.

  • vissvess's avatar
    vissvess
    Helper V
    7 years ago

    Hi Zubair_Muhammad ,

     

    Could you please suggest your view.

     

    Thanks

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community 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] )