Create an alert table based on multiple parameters

[water-guy-5]

I have some different locations, and we perform sort of "random" updates/checks on how many managers there are currently in the building. For each location, there are a few different maximums. Most are 2, but a few are 3. Which can be seen here. We can call this table 1.

I am trying to create a table that highlights when the manager count exceeds the limit for each location. Here is an example of what the routine checks might look like.



In this case, Chicago exceeded its limit. I am having trouble setting indivudal limits and the syntax associated with it. It should look something like this.

IF(
     AND("Location" <> {"Houston", "Los Angeles", "New York"}, "Amount > "Manager Limit"), 1, 0) where 1 is the flag of exceedance.

However, I also need a column for the locations where the limit is 3.
IF(
     AND("Location" = {"Houston", "Los Angeles", "New York"}, "Amount > "Manager Limit"), 1, 0)

How do i get this all into one column. Or can I have two seperate measures and out both as filters = 1 in the filters pane. Also, how do i get it to search multiple areas using the '{', I know it's possible I'm just messing it up.

Please let me know if you have any more questions. Thanks!

[MFelix · Accepted solution]

Hi @water-guy-5 ,

You can do this in two ways.

Having a calculated column for this you need  to relate both table by the location and then create the following calculated column:

Flag = IF(RELATED('Managers Limit'[Managers Limit]) < Checks[Amount], 1 , 0)

Second option is creation of a measure:

CheckLocations =
IF (
    CALCULATE (
        MAX ( 'Managers Limit'[Managers Limit] ),
        'Managers Limit'[Location] = MAX ( Checks[LocationChecked] )
    )
        < SUM ( Checks[Amount] ),
    1,
    0
)

This works for related or unrelated tables.