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

        Yes, Ofcourse. My data has unique ID of around 1k values.

         

        The transactions may go upto 16k+ and recurring.

         

        I'll take the point.

         

        Thanks

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      7 years ago

      Welcome Sir