Need Help - DAX Measure to Calculate the values depending on the temp Column

[Ibrahim_shaik]

Hi Power BI Community,

I have a table in Power BI where in the table there is a date column, time column with hourly timestamps, three columns named b35, b40, a40 which has per hour consumption values and a temp column which displays the temperature per hour.

I want to create a DAX Measure where in when the user selects a particular time period from the time slicer and Date from the Date Slicer or month, year. the measure should calculate the consumption for the selected time from the three columns depending on the temperature.

1. that is if in the selected time period is three hours then it should check the temperature if the temperature is below 35 for 1st hour and it should take the values from b35 and sum it and average.
2. for the Second hour it should check the temperature and calculate the value from b40 if it temp is above 35 and below 40 sum it and average.
3. for the third hour similarly if the temperature is above it should take the values from a40 sum it and average and the end result is sum the three averages and I want to display the result on the Card Visual or New Card Visual.

If the temperature is constantly below 35 it should only the b35 column and sum it and average value, same for the other two columns as well.

Please give a solution.

Thanks&Regards,

Ibrahim.

[Ibrahim_shaik]

Hi @123abc ,

Thank you so much for the response.

I will work apply the Measure and will let know you i have any issues.

Thanks.

[123abc]

To achieve this in Power BI, you can create a DAX measure using the following steps:

1. First, ensure you have your table loaded into Power BI with the necessary columns: Date, Time, b35, b40, a40, and temp.

2. Create a new measure by going to the Modeling tab, then clicking on New Measure.

3. Use the following DAX expression to create your measure:

SelectedPeriodConsumption =
VAR SelectedPeriod =
SELECTEDVALUE('Date'[Date]) // Assuming 'Date' is the name of your date column
VAR SelectedTime =
SELECTEDVALUE('Time'[Time]) // Assuming 'Time' is the name of your time column
VAR StartTime = SelectedTime
VAR EndTime = SelectedTime + TIME(1, 0, 0) // Assuming each period is one hour
RETURN
AVERAGEX(
FILTER(
'YourTableName',
'YourTableName'[Date] = SelectedPeriod &&
'YourTableName'[Time] >= StartTime &&
'YourTableName'[Time] < EndTime
),
SWITCH(
TRUE(),
MAX('YourTableName'[temp]) < 35, AVERAGE('YourTableName'[b35]),
MAX('YourTableName'[temp]) >= 35 && MAX('YourTableName'[temp]) < 40, AVERAGE('YourTableName'[b40]),
MAX('YourTableName'[temp]) >= 40, AVERAGE('YourTableName'[a40])
)
)

Replace 'YourTableName' with the name of your table.

1. Now, you can add a Card visual to your report canvas and use this newly created measure (SelectedPeriodConsumption) in it.

This measure will calculate the consumption based on the selected time period and temperature conditions as described in your requirements.

Make sure your date and time columns are formatted correctly as dates and times in Power BI to ensure accurate filtering.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.