Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
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
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |