Forum Discussion

henriquert's avatar
henriquert
New Member
6 years ago

LOOKUPVALUE with FILTER within RANGE

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.

Need Help
Tips and Tricks

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    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's avatar
      henriquert
      New Member
      6 years ago

      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.