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.

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. 

 

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

    Hi Anonymous ,

    Hope that your report will run smoothly.  If you have any questions, please feel free to ask us.

     

    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.