DAX lookupvalue with date condition

[harinik]

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.

[Alef_Ricardo_]

To achieve the desired result in Table 2, where you want to set the "child power outage" based on the conditions in Table 1, you need to filter the lookup based on both the meter's "power outage" flag and the selected "measurement date." Unfortunately, you can't directly use `LOOKUPVALUE` for this purpose. Instead, you can create a calculated column in Table 2 using DAX to achieve this. Here's a step-by-step approach to do this:

1. Create a new calculated column in Table 1 to associate each child meter with the measurement date where the parent meter had a power outage. This column will filter based on the "power outage" flag and the selected measurement date.

```DAX
Child Power Outage Date =
VAR SelectedDate = SELECTEDVALUE(Calendar[Date])
RETURN
IF(
CALCULATE(MAX('Table1'[power outage]),
FILTER('Table1', 'Table1'[power outage] = 1 && 'Table1'[date] = SelectedDate)) = 1,
1,
BLANK()
)
```

2. Now, in Table 2, you can create a calculated column "Child Power Outage" that references the "Child Power Outage Date" from Table 1.

```DAX
Child Power Outage = RELATED('Table1'[Child Power Outage Date])
```

This approach ensures that the "Child Power Outage" column in Table 2 is set to 1 only when the "power outage" flag in Table 1 is 1, and the associated measurement date matches the selected date from your calendar table.

Make sure that you have a valid relationship between your tables and that you are using the selected date from your calendar table correctly in the calculations.

[harinik]

Is this a calculated DAX column or measure?

[123abc]

The DAX formula I provided should be used as a calculated column in Table 2, not as a measure.

You should add this formula as a new calculated column in Table 2 to calculate the "child power outage" values based on the conditions mentioned in your original question. Calculated columns are used to create new columns within your table, while measures are used for aggregations and calculations at a summary level.

[harinik]

I tried this DAX formula as calculated column but it does not work for me. It shows only blanks.

[123abc]

To achieve your desired result, you can use a combination of DAX functions and filters to apply the date condition when performing the lookup. Here's a modified DAX measure for the "child_power_outage" column in Table 2 that takes into account both the power outage flag and the selected measurement date:

child_power_outage =

VAR SelectedDate = MAX('Calendar'[Calendar Date]) // Replace 'Calendar' with the actual name of your calendar table

RETURN

IF(

LOOKUPVALUE(

'Table1'[power outage],

'Table1'[Meter ID], [Parent ID],

'Table1'[date], SelectedDate,

'Table1'[power outage], 1

) = 1,

1,

BLANK()

)

This measure uses a variable, SelectedDate, to capture the selected measurement date from your calendar table. Then, it uses LOOKUPVALUE to search for a match in Table 1 based on the parent meter ID, the selected date, and the power outage flag. If a match is found with a power outage flag of 1, it returns 1 for the child power outage column in Table 2; otherwise, it returns a blank.

Make sure to replace 'Calendar' with the actual name of your calendar table in the code.

This DAX measure should give you the expected result where only child meters associated with a parent meter with a power outage flag set to 1 on the selected measurement date will have their child power outage flag set to 1 in Table 2.