Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vissvess
Helper V
Helper V

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

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Here you are:

 

 

View solution in original post

@vissvess 

 

Sample file attached as well

 

GCD.png


Regards
Zubair

Please try my custom visuals

View solution in original post

14 REPLIES 14
Anonymous
Not applicable

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

Hi @Zubair_Muhammad ,

 

Could you please suggest your view.

 

Thanks

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

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad ,

 

Thanks for the workaround.

Welcome Sir


Regards
Zubair

Please try my custom visuals

@vissvess 

 

Sample file attached as well

 

GCD.png


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

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

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
Not applicable

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

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.

Anonymous
Not applicable

Here you are:

 

 

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

Anonymous
Not applicable

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

Best
Darek

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors