Countrows with two conditions in different tables

[CH-BsAs]

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

[Anonymous]

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]

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]

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