Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Sample file attached as well
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 @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] )
Welcome Sir
Sample file attached as well
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
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.
Is there any chance of do it in a measure with multiple inputs?
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |