Forum Discussion
Calculation from Javascript with loop to DAX
Hello,
I have been asked to create the following calculation in DAX but I am facing difficulties to translate it in DAX because there are functions and also loops. Could you please help me?
let quantiles_dom = [0, 47, 75, 159, 233, 298, 358, 417, 476, 537, 603, 674, 753, 843, 949, 1076, 1237, 1459, 1801, 2479, 594601];
let quantiles_req = [0, 2, 15, 25, 34, 42, 49, 56, 63, 70, 78, 86, 95, 105, 117, 130, 147, 170, 205, 281, 3920];
let quantiles_size = [0, 1.37, 144.7, 319.53, 479.46, 631.97, 783.38, 937.91, 1098.62, 1265.47, 1448.32, 1648.27, 1876.08, 2142.06, 2465.37, 2866.31, 3401.59, 4155.73, 5400.08, 8037.54, 223212.26];
/**
Calcul ecoIndex based on formula from web site www.ecoindex.fr
**/
function computeEcoIndex(dom,req,size)
{
const q_dom= computeQuantile(quantiles_dom,dom);
const q_req= computeQuantile(quantiles_req,req);
const q_size= computeQuantile(quantiles_size,size);
return 100 - 5 * (3*q_dom + 2*q_req + q_size)/6;
}
function computeQuantile(quantiles,value)
{
for (let i=1;i<quantiles.length;i++)
{
if (value<quantiles[i]) return (i + (value-quantiles[i-1])/(quantiles[i] -quantiles[i-1]));
}
return quantiles.length;
}
function getEcoIndexGrade(ecoIndex)
{
if (ecoIndex > 75) return "A";
if (ecoIndex > 65) return "B";
if (ecoIndex > 50) return "C";
if (ecoIndex > 35) return "D";
if (ecoIndex > 20) return "E";
if (ecoIndex > 5) return "F";
return "G";
}
1 Reply
Hi Anonymous
Can you post sample data as text and expected output?
Not enough information to go on;
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
