Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How can i compare 2 tables(2 columns from different tables) using Date and/or Hour?

Table 1 give me one range of Hour/Date.   Table 2, give me the Hour/Date plus Velocity.   I have to compare this two tables to just consider data inside the range (from Table 1) according to date...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

    Sorry for late back. I find that you create a new thread and give a sample. So I modify the formula based on your pbix file. 

     

    For your sample, I think "Tempo" and "DataInicial" are date type. And you want them to be time type. You could use the function of TIMEVALUE to get the time values.  

     

    Start time = TIMEVALUE(tabela2[DataInicial])
End Time = tabela2[Start time] + tabela2[Horas (h)]

Time = TIMEVALUE(tabela1[Tempo])

     

     And then create a measure to get the Velocidade values.

    Note : There is no relationships between the tables.

     

    Measure = 
IF (
    MAX ( tabela1[Time] ) >= MAX ( tabela2[Start time] )
        && MAX ( tabela1[Time] ) <= MAX ( tabela2[End Time] ),
    MAX ( tabela1[Velocidade(km/h)] ),
    BLANK ()
)

     

     

    Please reference this blog to learn more about measure and calculated column.

    https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

     

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Column 1: Initial Date/Hour; Column 2: The range starting in the initial date (Final date = Initial Date + Range)Column 1 : Date/hour in the point (I have to filter that exactly on the range of Hour/Date from Table 1)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Im new on PBI.

 

Sorry for my english, is not my first language.

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

Hi Anonymous ,

I'm not clearly about compares two tables. I create a sample that you can reference and check if is what you want.

End Date = Table2[Start Date] + TIMEVALUE(Table2[Hours])

Measure =
IF (
    MAX ( Table1[Date] ) >= MAX ( Table2[Start Date] )
        && MAX ( Table1[Date] ) <= MAX ( Table2[End Date] ),
    MAX ( Table1[Velocity] ),
    BLANK ()
)

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    i did it, but my column doesn't repport anything, i think it conclude all is ''false'', this way, all things is ''blanked''. 

     

    Do u know what i did wrong?

    v-xuding-msft 

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

      Hi Anonymous ,

      Sorry for late back. I find that you create a new thread and give a sample. So I modify the formula based on your pbix file. 

       

      For your sample, I think "Tempo" and "DataInicial" are date type. And you want them to be time type. You could use the function of TIMEVALUE to get the time values.  

       

      Start time = TIMEVALUE(tabela2[DataInicial])
End Time = tabela2[Start time] + tabela2[Horas (h)]

Time = TIMEVALUE(tabela1[Tempo])

       

       And then create a measure to get the Velocidade values.

      Note : There is no relationships between the tables.

       

      Measure = 
IF (
    MAX ( tabela1[Time] ) >= MAX ( tabela2[Start time] )
        && MAX ( tabela1[Time] ) <= MAX ( tabela2[End Time] ),
    MAX ( tabela1[Velocidade(km/h)] ),
    BLANK ()
)

       

       

      Please reference this blog to learn more about measure and calculated column.

      https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

       

       

      Best Regards,

      Xue Ding

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks v-xuding-msft for your help.

         

        I wrote one formula that repport me and InitialDate and EndDate for each row, including the exactly Time(Tempo in my table) in a range (InitialDate and EndDate).

        This is the formula:

        InitialDate = CALCULATE(VALUES(tabela2[DataInicial]);FILTER(tabela2;tabela1[Tempo]>= tabela2[DataInicial] && tabela1[Tempo]<= tabela2[EndDate]))
         
        This one repport me InitialDate(DataInicial) for each row, but simply changing tabela2[DataInicial] for tabela2[EndDate], it repport me too the EndDate for each row.
         
        I dont tested ur way, but i think it works. Thanks for all help.
         
        Best regards, Daniel.