Forum Discussion
Double lookup for value
- Anonymous6 years ago
The problem i had could be tackeled by using these:
VAR lookupinlaattemplower = CALCULATE( MAX('Isentrope coefficient(k)'[Temp(k)]); FILTER(ALL('Isentrope coefficient(k)'[Temp(k)]);'Isentrope coefficient(k)'[Temp(k)] < inlaattemp))VAR lookupinlaatdruklower = CALCULATE( MAX('Isentrope coefficient(k)'[Druk(a)]); FILTER(ALL('Isentrope coefficient(k)'[Druk(a)]);'Isentrope coefficient(k)'[Druk(a)] < inlaatdruk))And to lookup with double value:
VAR isentropeinlaatlowertemplowerdruk = LOOKUPVALUE('Isentrope coefficient(k)'[IsentropeCoefficient];'Isentrope coefficient(k)'[Temp(k)];lookupinlaattemplower;'Isentrope coefficient(k)'[Druk(a)];lookupinlaatdruklower)
For people looking to do bilinear interpolation this is my final solution:isentropecoefficient = //Declaring variables. VAR inlaattemp = SELECTEDVALUE('Gemeten parameters'[Inlaattemp(k)]) VAR uitlaattemp = SELECTEDVALUE('Gemeten parameters'[Uitlaattemp(k)]) VAR inlaatdruk = SELECTEDVALUE('Gemeten parameters'[Inlaatdruk(a)]) VAR uitlaatdruk = SELECTEDVALUE('Gemeten parameters'[Uitlaatdruk(a)]) //Stappen in tabel VAR deltax = 5 VAR deltay = 5 //Calculating values in lookuptable lower then measured value. //Inlaat VAR lookupinlaattemplower = CALCULATE( MAX('Isentrope coefficient(k)'[Temp(k)]); FILTER(ALL('Isentrope coefficient(k)'[Temp(k)]);'Isentrope coefficient(k)'[Temp(k)] < inlaattemp)) VAR lookupinlaatdruklower = CALCULATE( MAX('Isentrope coefficient(k)'[Druk(a)]); FILTER(ALL('Isentrope coefficient(k)'[Druk(a)]);'Isentrope coefficient(k)'[Druk(a)] < inlaatdruk)) //Uitlaat VAR lookupuitlaattemplower = CALCULATE( MAX('Isentrope coefficient(k)'[Temp(k)]); FILTER(ALL('Isentrope coefficient(k)'[Temp(k)]);'Isentrope coefficient(k)'[Temp(k)] < uitlaattemp)) VAR lookupuitlaatdruklower = CALCULATE( MAX('Isentrope coefficient(k)'[Druk(a)]); FILTER(ALL('Isentrope coefficient(k)'[Druk(a)]);'Isentrope coefficient(k)'[Druk(a)] < uitlaatdruk)) //Calculating values in lookuptable higher then measured value. //Inlaat VAR lookupinlaattemphigher = CALCULATE( MIN('Isentrope coefficient(k)'[Temp(k)]); FILTER(ALL('Isentrope coefficient(k)'[Temp(k)]);'Isentrope coefficient(k)'[Temp(k)] > inlaattemp)) VAR lookupinlaatdrukhigher = CALCULATE( MIN('Isentrope coefficient(k)'[Druk(a)]); FILTER(ALL('Isentrope coefficient(k)'[Druk(a)]);'Isentrope coefficient(k)'[Druk(a)] > inlaatdruk)) //Uitlaat VAR lookupuitlaattemphigher = CALCULATE( MIN('Isentrope coefficient(k)'[Temp(k)]); FILTER(ALL('Isentrope coefficient(k)'[Temp(k)]);'Isentrope coefficient(k)'[Temp(k)] > uitlaattemp)) VAR lookupuitlaatdrukhigher = CALCULATE( MIN('Isentrope coefficient(k)'[Druk(a)]); FILTER(ALL('Isentrope coefficient(k)'[Druk(a)]);'Isentrope coefficient(k)'[Druk(a)] > uitlaatdruk)) //Looking up the isentrope in lookup table. //Inlaat VAR isentropeinlaatlowertemplowerdruk = LOOKUPVALUE('Isentrope coefficient(k)'[IsentropeCoefficient];'Isentrope coefficient(k)'[Temp(k)];lookupinlaattemplower;'Isentrope coefficient(k)'[Druk(a)];lookupinlaatdruklower) VAR isentropeinlaatlowertemphigherdruk = LOOKUPVALUE('Isentrope coefficient(k)'[IsentropeCoefficient];'Isentrope coefficient(k)'[Temp(k)];lookupinlaattemplower;'Isentrope coefficient(k)'[Druk(a)];lookupinlaatdrukhigher) VAR isentropeinlaathighertemplowerdruk = LOOKUPVALUE('Isentrope coefficient(k)'[IsentropeCoefficient];'Isentrope coefficient(k)'[Temp(k)];lookupinlaattemphigher;'Isentrope coefficient(k)'[Druk(a)];lookupinlaatdruklower) VAR isentropeinlaathighertemphigherdruk = LOOKUPVALUE('Isentrope coefficient(k)'[IsentropeCoefficient];'Isentrope coefficient(k)'[Temp(k)];lookupinlaattemphigher;'Isentrope coefficient(k)'[Druk(a)];lookupinlaatdrukhigher) //Uitlaat VAR isentropeuitlaatlowertemplowerdruk = LOOKUPVALUE('Isentrope coefficient(k)'[IsentropeCoefficient];'Isentrope coefficient(k)'[Temp(k)];lookupuitlaattemplower;'Isentrope coefficient(k)'[Druk(a)];lookupuitlaatdruklower) VAR isentropeuitlaatlowertemphigherdruk = LOOKUPVALUE('Isentrope coefficient(k)'[IsentropeCoefficient];'Isentrope coefficient(k)'[Temp(k)];lookupuitlaattemplower;'Isentrope coefficient(k)'[Druk(a)];lookupuitlaatdrukhigher) VAR isentropeuitlaathighertemplowerdruk = LOOKUPVALUE('Isentrope coefficient(k)'[IsentropeCoefficient];'Isentrope coefficient(k)'[Temp(k)];lookupuitlaattemphigher;'Isentrope coefficient(k)'[Druk(a)];lookupuitlaatdruklower) VAR isentropeuitlaathighertemphigherdruk = LOOKUPVALUE('Isentrope coefficient(k)'[IsentropeCoefficient];'Isentrope coefficient(k)'[Temp(k)];lookupuitlaattemphigher;'Isentrope coefficient(k)'[Druk(a)];lookupuitlaatdrukhigher) //X1_VAL = lowerdruk Y1_VAL = lowertemp //F11 = lowertemplowerdruk F21= lowertemphigherdruk F12= highertemplowerdruk F22=highertemphigherdruk VAR inlaatG1 = ((lookupinlaatdruklower + deltax - inlaatdruk) * isentropeinlaatlowertemplowerdruk + (inlaatdruk - lookupinlaatdruklower) * isentropeinlaatlowertemphigherdruk) / deltax VAR inlaatG2 = ((lookupinlaatdruklower + deltax - inlaatdruk) * isentropeinlaathighertemplowerdruk + (inlaatdruk - lookupinlaatdruklower) * isentropeinlaathighertemphigherdruk) / deltax VAR Interpolate_BL = ((lookupinlaattemplower + deltay - inlaattemp) * inlaatG1 + (inlaattemp - lookupinlaattemplower) * inlaatG2) / deltay RETURN //Checking if pressure is in bound. IF( OR( MAX('Gemeten parameters'[Inlaatdruk(a)]) < MIN('Isentrope coefficient(k)'[Druk(a)]); MIN('Gemeten parameters'[Inlaatdruk(a)]) > MAX('Isentrope coefficient(k)'[Druk(a)])) ;"Error Druk valt niet binnen lookup table."; //Checking if temperature is in bound. IF( OR( MAX('Gemeten parameters'[Inlaattemp(k)]) < MIN('Isentrope coefficient(k)'[Temp(k)]); MIN('Gemeten parameters'[Inlaattemp(k)]) > MAX('Isentrope coefficient(k)'[Temp(k)]));"Error Temperatuur valt niet binnen lookup table"; //Rest van code Interpolate_BL ) )
looking at values not able to make logic how are you relating two. Typically, when we need data from table2 into table1 we use something like these
New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])
New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project],table2[name])Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
For example the pressure is 61. Means i need the one below and above 61. (60 and 65). Then the temperature is 283. meaning also i need one below and above (280 and 285).
This way i can see that the combination of 60 and 280 create 1.343 and 65 and 285 create 1,354. With these numbers i can use Bilinear interpolation to calculate exact number for 61 and 283.