Forum Discussion

Gilson_Souza's avatar
Gilson_Souza
Frequent Visitor
4 years ago
Solved

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.

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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's avatar
    SpartaBI
    4 years ago

    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

     


    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

7 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Gilson_Souza
      Frequent Visitor
      4 years ago

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

    • Gilson_Souza's avatar
      Gilson_Souza
      Frequent Visitor
      4 years ago

      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!