Forum Discussion

Rudy_D's avatar
Rudy_D
Icon for Resolver I rankResolver I
4 years ago
Solved

Count matchs between two tables

Hi everyone ! 

 

I have some trouble to solve the problem below.

I have two unrelated tables :

Table 'Job'

 

IDStart JobEnd Job
102/20/2022 01:00 am02/20/2022 07:00 am
202/20/2022 07:00 am 02/20/2022 05:00 pm
303/15/2022 02:00 am03/15/2022 08:00 am
402/14/2022 02:00 am02/14/2022 08:00 am
1 02/21/2022 07:00 am 02/21/2022 05:00 pm 

 

Table 'Travel'

 

IDStart Travel
102/20/2022 03:00 am
202/20/2022 09:00 am 
102/21/2022 09:00 am
402/13/2022 02:00 am

 

I would like to calculate, for each ID and by month, how many times the 'Start Travel' time is between 'Start Job' and 'End Job'.

 

The expected result for February would be : 

 

IDResult
12
21
40

 

Thank you very much for your help.

 

Rudy

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    Rudy_D 

    maybe you can create a column

    isinscope =
    var _check=maxx(FILTER(Job,'Job'[ID]=Travel[ID]&&Job[Start Job]<='Travel'[Start Travel] && 'Job'[End Job]>='Travel'[Start Travel]),Travel[Start Travel])
    return if(ISBLANK(_check),0,1)
     
    pls see the attachment below

8 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Rudy_D 

    please try

    Result =
SUMX (
    Job,
    SUMX (
        Travel,
        IF (
            Travel[Start Travel] >= Job[Start Job]
                && Travel[Start Travel] <= Job[End Job],
            1
        )
    )
)
    • Rudy_D's avatar
      Rudy_D
      Icon for Resolver I rankResolver I
      4 years ago

      Hi !

       

      Thanks for your answer.

       

      I created the measure but don't know how to display it. How should I use it ?

       

      Rudy

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Rudy_D 

        Place the ID column from either of the tables in a table visual then place the measure in the table visual 

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    4 years ago

    Rudy_D 

    maybe you can create a column

    isinscope =
    var _check=maxx(FILTER(Job,'Job'[ID]=Travel[ID]&&Job[Start Job]<='Travel'[Start Travel] && 'Job'[End Job]>='Travel'[Start Travel]),Travel[Start Travel])
    return if(ISBLANK(_check),0,1)
     
    pls see the attachment below
    • Rudy_D's avatar
      Rudy_D
      Icon for Resolver I rankResolver I
      4 years ago

      Thanks a lot ! Apparently it works well :). I usually try to avoid calculated columns and I didn't know that you can filter a table using condition on fields from other unrelated tables. That's perfect.

       

      Thanks also to tamerj1 , I tried your measure but it was very long to process.