Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Img 2Img 2Img 1Img 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

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
v-diye-msft
Community Support
Community Support

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

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 

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.
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.