Create measure to return value from column when condition met for two other columns

[ck_ky]

I am new to PowerBI.

I am trying to return the minimum air temperature based upon the date and county that the minimum soil surface temperature is identified thru the VAR function.

Each county in the 'nasa power weather data' table has a single row of data for each date. So there should be a single value that is returned if I can get the syntax correct to identify the date/county.

The measure I have tried, but won't return a value for Minimum Air Temp

The Report view I am needing to display:

[v-hashadapu]

Hi @ck_ky , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[v-sgandrathi]

Hi @ck_ky,

If your measure is returning BLANK, it's often because the filter context isn't finding an exact row match between the two tables. Since your soil data is recorded every 5 minutes and the air data is hourly, the Date/Time fields likely don't line up perfectly.

Here are a few things to check:

1. Ensure both tables use the same Date column datatype and format.
   • One table might have full DateTime values, while the other only has Dates.
   • Even a hidden time component can prevent a match.
2. Try matching only on the Date part, not the full DateTime.
3. Confirm that county names match exactly in both tables.
   • Extra spaces or differences in capitalization can also result in BLANK values.

You can test with a simplified formula like this:

Min Daily Air Temp =
VAR _MinSoil =
    MIN ( 'Soil.Temp'[Soil Surface Temperature (F)] )

VAR _Date =
    CALCULATE (
        MIN ( 'Soil.Temp'[Date] ),
        FILTER (
            'Soil.Temp',
            'Soil.Temp'[Soil Surface Temperature (F)] = _MinSoil
        )
    )

VAR _County =
    CALCULATE (
        SELECTEDVALUE ( 'Soil.Temp'[County Name only] ),
        FILTER (
            'Soil.Temp',
            'Soil.Temp'[Soil Surface Temperature (F)] = _MinSoil
        )
    )

RETURN
CALCULATE (
    MIN ( 'NASA Power Weather Data'[MinTempF] ),
    FILTER (
        'NASA Power Weather Data',
        'NASA Power Weather Data'[County Name only] = _County
            && DATEVALUE ( 'NASA Power Weather Data'[Date] ) = DATEVALUE ( _Date )
    )
)

If you still get BLANK, try creating a temporary table visual displaying:

• Soil Temp Date
• Air Temp Date
• County from both tables
• The calculated variables

This can help pinpoint which field isn't matching between the tables.

Thank you.

[cengizhanarslan]

Use CALCULATE with explicit filters on both County and Date instead of LOOKUPVALUE:

Min Daily Air Temp for Minimum Surface Temperature =
VAR _MinSurf =
    MIN ( 'Soil.Temp'[Soil Surface Temperature (F)] )
VAR _MinSurfDate =
    CALCULATE (
        SELECTEDVALUE ( 'Soil.Temp'[Date] ),
        'Soil.Temp'[Soil Surface Temperature (F)] = _MinSurf
    )
VAR _MinSurfCounty =
    CALCULATE (
        SELECTEDVALUE ( 'Soil.Temp'[County Name only] ),
        'Soil.Temp'[Soil Surface Temperature (F)] = _MinSurf
    )
RETURN
    CALCULATE (
        MIN ( 'NASA Power Weather Data'[MinTempF] ),
        'NASA Power Weather Data'[County Name only] = _MinSurfCounty,
        'NASA Power Weather Data'[Date] = _MinSurfDate
    )

[ck_ky]

Thank you for this suggestion.

However, I am still getting "BLANK" for the card.

Is there something with my data that may be causing this?

[Shai_Karmani]

Try this pattern, replacing the column names with your actual ones:

Air Temp at Min Soil =
VAR MinSoil = MIN('nasa power weather data'[Soil Surface Temp])
RETURN
CALCULATE(
    MAX('nasa power weather data'[Air Temperature]),
    'nasa power weather data'[Soil Surface Temp] = MinSoil
)

MIN respects the current filter context, and CALCULATE narrows the table to rows matching that minimum, so the inner MAX returns the air temp on the row with the lowest soil temp. If two rows ever tie on min soil temp, swap MAX for MIN, or use TOPN to pick exactly one row.

If this works for you, kindly mark it as the solution and give a thumbs up.

Thanks,
Shai Karmani

[ck_ky]

This will not work. The air temps are in a separate table from soil surface temps because the air temps are recorded once per hour and the soil surface temps are recorded every 5 minutes (12 times per hour).

So in my mind I need to identify the county and date that the minimum soil temperature was recorded in one table, and then 'search' another table for the air temperature in that county and on that date that will be displayed in the card on the dashboard.

[krishnakanth240]

Hi @ck_ky 

Will this work

Min Air Temp at Min Soil =

VAR MinSoilRow = TOPN(1,'Soil Temp','Soil Temp'[Soil Surface Temperature (F)], ASC)

VAR MinDateTime = MAXX(MinSoilRow, 'Soil Temp'[DateTime])

VAR MinCounty = MAXX(MinSoilRow, 'Soil Temp'[County Name only])

RETURN

CALCULATE(MAX('Air Temp'[Air Temperature]),'Air Temp'[County Name only] = MinCounty,DATEVALUE('Air Temp'[DateTime]) =DATEVALUE(MinDateTime))

[ck_ky]

thanks for this suggestion.

However, dax won't 'allow' 'air temp' [county name only] to be an option in the CALCULATE function.