Forum Discussion

ck_ky's avatar
ck_ky
New Member
4 months ago
Solved

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

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 i...
  • v-sgandrathi's avatar
    v-sgandrathi
    4 months ago

    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.

Shai_Karmani's avatar
Shai_Karmani
Icon for Super User rankSuper User
4 months ago

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's avatar
    ck_ky
    New Member
    4 months ago

    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's avatar
      krishnakanth240
      Icon for Super User rankSuper User
      4 months ago

      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's avatar
        ck_ky
        New Member
        4 months ago

        thanks for this suggestion.

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