LOOKUPVALUE with FILTER within RANGE

[henriquert]

Hello guys,

I need some help with DAX logic on this one..

First an overview of the situation: I have one table ("ALERTAS") which contains various security alerts. This table is indexed by the driver name, but sometimes it does not give me the name, and the driver is then hidden, showing up as "NA". I have another table ("MACRO") which contains the start ("inicio") and end ("fim") of each driver's trip with a given license plate. In order to discover who was driving the vehicle the moment the security alert was generated, I want to lookup who the driver was in the "MACRO" table for the license plate and the moment in which the security alert was generated.

The first table is called "ALERTAS" and contains a driver ("motorista") column, a license plate ("placa") column and a date & time stamp ("Hora de Início")

I have another table called "MACRO" which contains a driver ("nome") column, a license plate ("VEICULO") column, a macro category ("INICIO/FIM") and a date & time stamp ("data inicio").

So basically what I need is:

to find WHO "NA" is in the "Alertas" table by looking up on "MACRO" table, matching the license plate and the time range between start (início) and end (fim)

I want to create a column inside the "ALERTAS" table which does the following:

1. Matches the license plate &&
2. For INICIO/FIM = INICIO, ALERTAS[Hora de início] >= MACRO[data inicio] &&
3. For INICIO/FIM = FIM, ALERTAS[Hora de início] < MACRO[data inicio]
4. RETURNS the driver name ("nome") from the MACRO table (there will never be more than one result)

I really need help with this, I have been trying different LOOKUPVALUE() and CALCULATE() functions for a few days now and have not gotten the result I need..

Please help

Thank you,

Henrique.

[ryan_mayu]

@henriquert 

I created sample data for this.

you can try to create a column

Column = 
VAR _type=MAXX(FILTER('MACRO','ALERTAS'[Placa]=MACRO[VEICULO]),'MACRO'[INICIO/FIM])
return if(_type="FIM",MAXX(FILTER('MACRO','ALERTAS'[Hora de inicio]<'MACRO'[data inicio]&&ALERTAS[Placa]=MACRO[VEICULO]),MACRO[nome]),MAXX(FILTER('MACRO','ALERTAS'[Hora de inicio]>='MACRO'[data inicio]&&ALERTAS[Placa]=MACRO[VEICULO]),MACRO[nome]))

hope this is helpful

[henriquert]

Thank you for the reply, @ryan_mayu 

I was unable to run the command due to insufficient memory..

I forgot to mention that both my tables are enormous and contain many thousands of lines.