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 ...
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
    • vissvess's avatar
      vissvess
      Icon for Helper V rankHelper 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