Create a dax to filter only last four hours and lookup a column

[sivarajan21]

Hi Team,

I have below sample data:

We need to create a dax using below logics:

• filter HeatSense_Device table for last four hours(use 'UpdatedOn'(max&min datetime) column for this)
• Then look if the 'UserMode' contains 'heat' in all rows.
• If the above 2 conditions are true then return "Heating on Last 4 Hours" else blank.

We tried to create the below logic but its not giving correct output:

Last 4 Hours in Heat = 
 
var latestdatetime = max(HeatSense_Device[UpdatedOn])
var earliestdatetime = latestdatetime-4/24
 
var last4hours =
CALCULATETABLE(HeatSense_Device,HeatSense_Device[UpdatedOn]<=latestdatetime && HeatSense_Device[UpdatedOn]>=earliestdatetime)
 
var maximummode = MAXX(last4hours,HeatSense_Device[UserMode])
var minimummode = MINX(last4hours,HeatSense_Device[UserMode])
 
return
if(and(maximummode="heat",minimummode="heat"),"Heating on Last 4 Hours",BLANK())

Ouput:

When I bring in UpdatedOn column into visual to test the above dax, it retuns the whole 24hours instead of last four hours. I want to show only last four hours where there is heat in table.

Could you please help us in creating a dax or modify the above dax ?

PFA file here Heatsense - Copy.pbix

Please advise!

Thanks in advance!

@marcorusso @Ahmedx @Greg_Deckler @Jihwan_Kim @jgeddes 

[Anonymous · Accepted solution]

Hi @sivarajan21 ,

Do you want this?

1.Get the UpdateOn column latest time, and filter rows between the latest time and four hours before the latest time.

2.Filter UserMode equals "heat".

If so, please try below dax. If not, please provide sample data because the link to pbix file you provided can't be opened.

Last 4 Hours in Heat = 

var latestdatetime = MAXX(ALL('Table'),'Table'[UpdatedOn])//Get latest time of UpdateOn

var earliestdatetime = latestdatetime-4/24//Get first 4 hours of latest time. 

return 

IF(MAX('Table'[UpdatedOn])>=earliestdatetime&&MAX('Table'[UpdatedOn])<=latestdatetime&&MAX('Table'[UserMode])="heat","Heating on Last 4 Hours",BLANK())

//Check UpdateOn is between latest time and earliest time, and UserMode is heat.

Best regards,

Mengmeng Li

[OktayPamuk80]

Hi Sivarajan,

You created a measure I guess. As it is "more" a static value, did you try to create a calculated column instead of a measure?

Regards,
Oktay

[sivarajan21]

Hi @OktayPamuk80 ,

Thanks for your quick response!

No we wanted a dax measure not a calculated column!

This is only a sample data attached. but we have tables with millions of rows behind.

Thanks in advance!

@marcorusso @Greg_Deckler @OktayPamuk80