DirectQuery filter table based on condition

[Kbdm]

I want to be able to create a filter on this table to get all rows with Type Z with no entry Type X for the same location within 1 hour before the Datetime of a Type Z.

So after filtering below table the result should only show Row 4(there is no entry with Type x within 1 hour before the Datetime)

Row #	Datetime	Location	Type
1	12-11-2020 13:15	A	X
2	12-11-2020 11:15	A	X
3	12-11-2020 12:00	A	Z
4	12-11-2020 11:00	A	Z

[v-kelly-msft · Accepted solution]

Hi  @Kbdm ,

Create a measure as below:

_Datetime = 
var _previousX=CALCULATE(MAX('Table'[Datetime]),FILTER(ALL('Table'),'Table'[Datetime]<MAX('Table'[Datetime])&&'Table'[Type]="X"&&'Table'[Location]=MAX('Table'[Location])))+0
Return
IF(MAX('Table'[Type])="Z"&&DATEDIFF(_previousX,MAX('Table'[Datetime]),HOUR)>1,MAX('Table'[Datetime]),BLANK())

And you will see：

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Kbdm , Try a measure like

measure =
if(isblank(calculate(countrows(Table), filter(All(Table), table[Datetime] < max(table[Datetime]) && table[Datetime] >= max(table[Datetime])-time(1,0,0)))),1,0)

[Kbdm]

@amitchandak Thank you for the answer so far, this covers the time within 1 hour requirement. I also need to be able to make sure that the locations in the resulting rows are the same and check the type combination Type Z should not have a Type X that has taken place earlier , however I am unable to use the EARLIER function for this. Any suggestion How I would go about this?