Forum Discussion
Return value based on hour range
Hi, everyone! I need some help, please.
I have two tables that have the listing of events that occurred at certain times. Here's a simplified version of the data file:
Table A:
Table B:
I need to return the ID value to Table A in case some criteria are met:
The cod column matches
If the table B event occurred between the -2 hour and +2 hour range of the table A event.
So the result would be:
Thanks in advance for your help.
Gilson_Souza create this caclculated column in table A:
ID = VAR _current_cod = 'Table A'[COD] VAR _current_date_time_a = 'Table A'[DATETIME] VAR _table = FILTER( 'Table B', VAR _current_date_time_b = 'Table B'[DATETIME] RETURN 'Table B'[COD] = _current_cod && ABS(_current_date_time_a - _current_date_time_b ) <= 0.083333333 ) VAR _result = CONCATENATEX(_table, 'Table B'[ID]) RETURN _resultGilson_Souza write this:
ID = VAR _current_cod = 'Table A'[COD] VAR _current_date_time_a = 'Table A'[DATETIME] VAR _table = FILTER ( 'Table B', VAR _current_date_time_b = 'Table B'[DATETIME] RETURN 'Table B'[COD] = _current_cod && _current_date_time_a - _current_date_time_b <= 0.0416666667 && _current_date_time_a - _current_date_time_b >= - 0.125 ) VAR _result = CONCATENATEX ( _table, 'Table B'[ID] ) RETURN _result
7 Replies
Gilson_Souza create this caclculated column in table A:
ID = VAR _current_cod = 'Table A'[COD] VAR _current_date_time_a = 'Table A'[DATETIME] VAR _table = FILTER( 'Table B', VAR _current_date_time_b = 'Table B'[DATETIME] RETURN 'Table B'[COD] = _current_cod && ABS(_current_date_time_a - _current_date_time_b ) <= 0.083333333 ) VAR _result = CONCATENATEX(_table, 'Table B'[ID]) RETURN _resultSpartaBI Worked perfectly! I have no words to thank you, I was trying to solve this for days. Thank you very much!
Gilson_Souza my pleasure 🙂
Hi, SpartaBI
Can you change the condition a little bit?
Instead:
If the table B event occurred between the -2 hour and +2 hour range of the table A event.
It will be:
If the table B event occurred between the -1 hour and +3 hour range of the table A event.
Thank you!Gilson_Souza write this:
ID = VAR _current_cod = 'Table A'[COD] VAR _current_date_time_a = 'Table A'[DATETIME] VAR _table = FILTER ( 'Table B', VAR _current_date_time_b = 'Table B'[DATETIME] RETURN 'Table B'[COD] = _current_cod && _current_date_time_a - _current_date_time_b <= 0.0416666667 && _current_date_time_a - _current_date_time_b >= - 0.125 ) VAR _result = CONCATENATEX ( _table, 'Table B'[ID] ) RETURN _result
