cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

Sem título.pngImg 2Sem título2.pngImg 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
Community Support

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

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:

7.PNG

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.

View solution in original post

4 REPLIES 4
Community Support
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''. 

 

@v-diye-msft 

Community Support
Community Support

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

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:

7.PNG

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.

View solution in original post

Anonymous
Not applicable

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

Thanks @v-diye-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, 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. 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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