topic Re: GCD for multiple values in DAX Commands and Tips

GCD for multiple values

vissvess — Mon, 19 Aug 2019 14:08:03 GMT

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 Request	Time	ID	Request	Confirmed
43287	6:09:32 PM	ALKDE	4	18
78744	6:37:49 PM	SDKED	3	20
46447	10:33:38 AM	ALKDE	5	36
87118	11:39:09 AM	ADKED	4	10
25660	2:38:27 AM	CNEDD	2	12
36598	5:42:56 AM	ALKDE	7	27
76068	9:40:03 AM	SDKED	10	15
89334	8:51:45 PM	ADKED	4	50

ID	Desc	Pen Size
SDKED	Storage	5
ADKED	Bottle	10
CNEDD	Bag	12
ALKDE	Couch	9

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

Re: GCD for multiple values

Anonymous — Tue, 20 Aug 2019 05:26:12 GMT

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

Re: GCD for multiple values

vissvess — Tue, 20 Aug 2019 05:30:11 GMT

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

Re: GCD for multiple values

Anonymous — Tue, 20 Aug 2019 05:38:01 GMT

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

Re: GCD for multiple values

vissvess — Tue, 20 Aug 2019 05:43:59 GMT

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.

Re: GCD for multiple values

vissvess — Tue, 20 Aug 2019 05:45:45 GMT

Hi Zubair_Muhammad ,

Could you please suggest your view.

Thanks

Re: GCD for multiple values

Anonymous — Tue, 20 Aug 2019 05:56:14 GMT

I know how to do it in PQ. Just need to set it all up. Bear with me.

Best
Darek

Re: GCD for multiple values

Anonymous — Tue, 20 Aug 2019 06:47:12 GMT

Here you are:

Re: GCD for multiple values

Zubair_Muhammad — Tue, 20 Aug 2019 06:51:03 GMT

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

Re: GCD for multiple values

Zubair_Muhammad — Tue, 20 Aug 2019 06:54:59 GMT

vissvess

Sample file attached as well

Re: GCD for multiple values

Anonymous — Tue, 20 Aug 2019 06:57:10 GMT

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

Re: GCD for multiple values

vissvess — Tue, 20 Aug 2019 07:27:28 GMT

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

Re: GCD for multiple values

vissvess — Tue, 20 Aug 2019 07:28:38 GMT

Zubair_Muhammad ,

Thanks for the workaround.

Re: GCD for multiple values

Zubair_Muhammad — Tue, 20 Aug 2019 07:36:04 GMT

Welcome Sir

Re: GCD for multiple values

AlePeraltaM — Sun, 23 Jul 2023 00:18:52 GMT

Is there any chance of do it in a measure with multiple inputs?