cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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''.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

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

Hi @Anonymous

``````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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
4 REPLIES 4
Community Support

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

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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

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

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''.

Community Support

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

Hi @Anonymous

``````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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

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

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, and changing again, it can repport me the other columns.

I dont tested ur way, but i think it works. Thanks for all help.

Best regards, Daniel.

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors