Forum Discussion
Return value based on hour range
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
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook
Showcase Report – Contoso By SpartaBI
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!
Community ChampionGilson_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
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook
Showcase Report – Contoso By SpartaBI
SpartaBI Thank you again! This formula worked perfectly 😀
- SpartaBI
Gilson_Souza my pleasure 🙂
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas.
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂