Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How can i compare 2 tables(2 columns from different tables) using range of Hour?

Img 2Img 1

 

I need to put and Initial Date (Data Inicial), and End Date (Data Inicial + Horas) in each row of Table 1, considering the Initial Date and End Date from table 2.

 

I wrote this formula show in table 1, i dont know if its right, but my column doesnt repport me anything. I think it conclude all is ''false'', this way, all things is ''blanked''. 

 

 

 

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi Anonymous 

     

    Please add the measure below:

    Measure 2 = 
VAR a =
    TIME ( HOUR ( MAX ( tabela1[Tempo] ) ), MINUTE ( MAX ( tabela1[Tempo] ) ), SECOND ( MAX ( tabela1[Tempo] ) ) )
VAR c =
    CALCULATE (
        COUNTROWS ( tabela2 ),
        FILTER (
              tabela2 ,
            TIME ( HOUR ( tabela2[DataInicial] ), MINUTE ( tabela2[DataInicial] ), SECOND ( tabela2[DataInicial] ) ) <= a
                && TIME ( HOUR ( tabela2[EndDate] ), MINUTE ( tabela2[EndDate] ), SECOND ( tabela2[EndDate] ) ) >= a
        )
    )
RETURN
IF ( c = BLANK (), BLANK (), SUM ( tabela1[Velocidade(km/h)] ) )

    Then remove the blank:

    Pbix attached.

     

4 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

     

    You might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      https://www.dropbox.com/transfer/AAAAAELW644OQNmBpQhndTrgXKL8GhEfDsR2qoUz9q_IdZxYDzRl1bY

       

      this way?

       

      As i said:

      I need to put and Initial Date (Data Inicial), and End Date (Data Inicial + Horas) in each row of Table 1, considering each value of Tempo (Table 1) included in the range formed by the Initial Date and End Date from table 2.

       

      I wrote this formula shown in table 1 (PontoDeInteresse), i dont know if its right, but my column doesnt repport me anything. I think it conclude all is ''false'', this way, all things is ''blanked''. 

       

      v-diye-msft 

      • v-diye-msft's avatar
        v-diye-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous 

         

        Please add the measure below:

        Measure 2 = 
VAR a =
    TIME ( HOUR ( MAX ( tabela1[Tempo] ) ), MINUTE ( MAX ( tabela1[Tempo] ) ), SECOND ( MAX ( tabela1[Tempo] ) ) )
VAR c =
    CALCULATE (
        COUNTROWS ( tabela2 ),
        FILTER (
              tabela2 ,
            TIME ( HOUR ( tabela2[DataInicial] ), MINUTE ( tabela2[DataInicial] ), SECOND ( tabela2[DataInicial] ) ) <= a
                && TIME ( HOUR ( tabela2[EndDate] ), MINUTE ( tabela2[EndDate] ), SECOND ( tabela2[EndDate] ) ) >= a
        )
    )
RETURN
IF ( c = BLANK (), BLANK (), SUM ( tabela1[Velocidade(km/h)] ) )

        Then remove the blank:

        Pbix attached.