Return value based on hour range

[Gilson_Souza]

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.

[SpartaBI · Accepted solution]

@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
    _result

Showcase Report – Contoso By SpartaBI

     

[SpartaBI · Accepted solution]

@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
    _result

Showcase Report – Contoso By SpartaBI

     

[Gilson_Souza]

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!

[SpartaBI · Accepted solution]

@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

      

Showcase Report – Contoso By SpartaBI

[Gilson_Souza]

@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 🙂

[Gilson_Souza]

@SpartaBI Worked perfectly! I have no words to thank you, I was trying to solve this for days. Thank you very much!

[SpartaBI]

@Gilson_Souza my pleasure 🙂