Forum Discussion

CH-BsAs's avatar
CH-BsAs
Frequent Visitor
8 years ago

Countrows with two conditions in different tables

I have a table with services that are performed in x amount of time (Time [response]) and I have anothe table with color codes (Services [color]) where each one has a time limit of response.


R = 5 min limit
Y = 10 min limit
G = 15 min limiy

 

I need to count how many services of each color have exceeded their limit and add them in a total number.

 

(services[color] = R && Time[response]>5) + (services[color] = Y && Time[response]>10) + (services[color] = G && Time[response]>15)

 

when I analyze them separately, for example for R Codes, I count how many times have [response]> 5 and then I apply a visual filter for R. This works fine, but this does not work when I want to add all the cases with its different standards.

 

I hope this makes sense. Excuse me for my english not my native language
Thanks in advance for the help

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi CH-BsAs,

     

    Maybe you can try to use below measure to calculate total row count:

     

    Color Count =
    COUNTROWS ( FILTER ( ALL ( Time ), [color] = "R" && [response] > 5 ) ) //R
     + COUNTROWS ( FILTER ( ALL ( Time ), [color] = "Y" && [response] > 10 ) ) //Y
     + COUNTROWS ( FILTER ( ALL ( Time ), [color] = "G" && Time[response] > 15 ) )//G

     

    Regards,

    Xiaoxin Sheng

    • CH-BsAs's avatar
      CH-BsAs
      Frequent Visitor
      8 years ago

      Thank you for your help Xiaoxin,

       

      The problem I have is that the data is located in two different tables.

      I think I did not explain myself the lasta time, since I see your formula is not taking that into account.

       

      one table is TIME with the RESPONSE field (how long it takes to respond to the case)
      and the other table is SERVICES with the field COLOR (type of service)

       

      what I have to do is, according to the standard of each service (in my first post) to count those that were exceeded and add them to all in a final record.

       

      there is any way to adapt your formula to two diferente tables?

      thank you very much!

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI CH-BsAs

         

        Can you please provide table structure and relationship between these tables? (share some some sample data and expected result will help for testing and coding formula)

         

        Regards,

        Xiaoxin Sheng