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 case it will have 1 to many values in it.

 

Kindly please anyone suggest any workaround for GCD (Greatest Common Divisor).

 

The data set is as follows.

Material RequestTimeIDRequestConfirmed
432876:09:32 PMALKDE418
787446:37:49 PMSDKED320
4644710:33:38 AMALKDE536
8711811:39:09 AMADKED410
256602:38:27 AMCNEDD212
365985:42:56 AMALKDE727
760689:40:03 AMSDKED1015
893348:51:45 PMADKED450

 

IDDescPen Size
SDKEDStorage5
ADKEDBottle10
CNEDDBag12
ALKDECouch9

 

In this Table 1 is the data table. table 2 to be added with a custom calculated column, where the two tables are related with ID column in both tables. The underlined column is the new column to be added based on table 1.

 

The result is as follows. For ALKDE = 9 (GCD of 18, 27 & 36).

 

Any kind help would be much appreciated.

 

Thanks

14 Replies

  • 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
      • 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] )