DAX lookupvalue with date condition
Hello, I want to lookupvalue based on a particular date. There are 2 tables. Table 1 with meter_id, power outage column and measurement date. Table 2 with child meters associated with the meter_id from table 1. Now I want to lookupvalue in table 2 based on following conditions: 1. Meter_id with power outage flag set to 1 in table 1 2. Lookup values only for meter_ids with the selected measurement date. The measurement date is linked to a calendar table and the calendar date from calendar table is used to select the measurement date. Finally in table 2 I want to set a power outage flag for those child meters associated with the meter_id from table 1 For example: If the selected date : 10.5.2023, then only the 100A meter with power outage flag set to 1 should be used to lookup value in table 2 and its corresponding child ABC1 should have the child power outage set to 1. The other child meters shouldn't be set to 1 because the power outage for its main meter was not on 10.5.2023 Table 1 Meter ID date power outage 100A 10.5.2023 1 1000B 11.6.2023 1 123C 22.11.2022 145D 13.5.2023 1 Table 2 Meter ID Parent ID child power outage ABC1 100A 1 X1008 1000B XV200 145D XY100 146E Currently, I wrote a DAX function in table 2 as follows: child_power_outage = LOOKUPVALUE(table 1[power_outage],table 1[Meter ID],[Parent ID],table 1[power_outage],1) But this gives: Table 2 Meter ID Parent ID child power outage ABC1 100A 1 X1008 1000B 1 XV200 145D 1 XY100 146E Is there a way to include a date condition or date filter in lookupvalue. ? I have tried merging table 1 and table 2, but power query is not allowing it because table 1 has other queries merged with it and it says it references other queries, so it may not directly access a data source, so rebuild this data combination. Any little bit of help is much appreciated. I am stuck at this problem for a long time. Thanks a lot.
A row measure with lookup features
I am trying to solve a kind of lookup-value problem in PowerBI. My goal is to calculate a measure that can extract spesific values based on their rownumber (dynamically). The values I want are in the column "Salary", and the number of rows to count down is calculated in the measure "Upper CI". The table will change when applying filters, but "SerialNO" is a dynamically calculated index. "SerialNO" might be relevant (as a lookup value for Salary - since it is equivalent to the rownumber in the dynamic table) or not. The best way to describe the problem might be to illustrate it with the desired solution. The table below contain the top 9 rows in my datatable. So, I am trying to calulate a measure corresponding to the column "Lookup result" (last column in the table). For example in the first row, the value for "Upper CI" is 3, hence I want the third row value from "Salary", that is "362000". In the second row the "Upper CI" is 5, hence I want the fifth row value from "Salary", that is 364 000. And so on.... Hope someone is able to solve this one. Thanks so much for any help! Salary SerialNO UpperCI Lookup result 350000 1 3 362000 360000 2 5 364000 362000 3 6 366000 364000 4 8 364000 5 10 366000 6 12 371000 7 13 373000 8 14 375000 9 16