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 up values in the next/previous row is a problem because power bi does not do it natively.
However, there is a standard way to do it. This depends on having a sequential index column, which you have in your ID column.
1. store the value of the ID column in the current row in a variable, using SELECTEDVALUE
VAR cur_id = SELECTEDVALUE(measured[ID])
2. add or subtract one from cur_id, depending on whether you want next/previous
3. use LOOKUPVALUE() to look up the value in the column you want with the new id value as a criteria
you could also use FILTER() to return the value in the column you want, since it will return only the 1 row that matches the new id value.
I'm a personal Power Bi Trainer I learn something every time I answer a question. I blog at http://powerbithehardparts.com/
The Golden Rules for Power BI
- Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
- Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
- Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
- Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result to check on your steps along the way.