Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Heya,
I have the following data sets:
Measured:
ID | Pressurein(a) | Tempin(k) | PressureOut(a) | Tempout(k) |
1 | 61,01325 | 283,15 | 101,01325 | 328,15 |
2 | 66,01325 | 288,15 | 106,01325 | 333,15 |
3 | 71,01325 | 293,15 | 111,01325 | 338,15 |
And lookup:
Temp(k) | Pressure(a) | Isentrope |
280 | 60 | 1,343981439 |
285 | 65 | 1,354300984 |
290 | 70 | 1,364754253 |
295 | 75 | 1,375176387 |
The idea is to get multiple values out of this. It may be multiple measures as i can refer to them in a future calculation.
So when Pressure and tempin are being looked at. the idea is that i get:
ID | Pressurein(a) | Tempin(k) | PressureOut(a) | Tempout(k) | LookupTemp(low) | LookupTemp(High) | Lookuppressure(low) | Lookuppressure(High) | Isentrope(low) | Isentrope(high) |
1 | 61,01325 | 283,15 | 101,01325 | 328,15 | 280 | 285 | 60 | 65 | 1,343981439 | 1,354300984 |
2 | 66,01325 | 288,15 | 106,01325 | 333,15 | 285 | 290 | 65 | 70 | 1,354300984 | 1,364754253 |
3 | 71,01325 | 293,15 | 111,01325 | 338,15 | 290 | 295 | 70 | 75 | 1,364754253 | 1,375176387 |
And then i can do the same for Output.
I do not know how to lookup values above and below in a certain column. I did found similar questions but they just wantent the ¨Closest¨ value which is not true in my case.
I hope someone can point me in the right direction.
Quality over Quantity
Solved! Go to Solution.
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
)
)
Quality over Quantity
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.
Quality over Quantity
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
Help when you know. Ask when you don't!
@kentyler
Thanks, but im not quite sure you understand what i mean.
The lookup table has no id's.
In case of storing i could store the selected ID, Temperature and pressure (1, 283 and 61)
Then i would have to lookup the closes value of Temperature BELOW 283 and the one ABOVE 283 (280 and 285 in the example)
Same for Pressure.
Then i need to use the Below values to get a number (1,343) and the above numbers to get the value (1.354) i believe this could be achieved with a filter statement indeed.
My problem lays in finding the closest value above and below.
Quality over Quantity
Wait its actually pretty easy!
If i use:
CALCULATE(
MAX('Isentrope coefficient(k)'[Temp(k)]);
FILTER(ALL('Isentrope coefficient(k)'[Temp(k)]);'Isentrope coefficient(k)'[Temp(k)] < inlaattemp))
I could use the same with MIN and a > filter. You guys helped me very well 🙂
I now have the Higher and lower values and im going to try to filter out the Isentrope
Thanks again!
(i will post my full solution for Bilinear Interpolation when im fully done in this topic for other people to find)
Quality over Quantity
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
)
)
Quality over Quantity
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |