How can I determine if a specific value in one table is in/out of a group in a different table?

[cb757]

I need to show if certain events happened in a user's assigned territory. I have a table of events and a separate table of territories (zip/postal codes). I need to evealuate each individual event and determine if it occured in territory or out of territory. Result will be used for conditional formatting and filtering.

The territory table can have multipe users assigned to one zip, so I thought I may need a key (userID + zip) for accuracy.

Event table

userID	event	zip	key
ab101	1001	40019	ab10140019
ab101	1002	55101	ab10155101
ab101	1003	55103	ab10155103
ab101	1004	23440	ab10123440

Territory Table 

userID	zip	key
ab101	55101	ab10155101
ab101	55102	ab10155102
ab101	55103	ab10155103
ab101	55104	ab10155104

In this scenario, rows 2 & 3 from the event table would match and be considered 'IN' and 1 & 4 would be 'OUT'. Any help is appreciated.

[cb757]

nk you for the replies. I was able to use what was provided to create a solutions that worked for this project. Really appreciate the assist

[Anonymous]

Hi @cb757 ,

You can create a calculated column as below in Event table to get it, please find the details in the attachment.

Column = 
VAR _count =
    COUNTROWS (
        FILTER (
            'Territory',
            'Territory'[userID] = 'Event'[userID]
                && 'Territory'[zip] = 'Event'[zip]
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( _count ) ), "IN", "OUT" )

Best Regards

[DataNinja777 · Accepted solution]

Hi @cb757 ,

You can determine whether an event occurred inside or outside a user's assigned territory by checking if the event's key (a combination of userID and zip) exists in the Territory table. A calculated column can be used for this purpose:

In_Territory = 
IF(
    LOOKUPVALUE('Territory Table'[key], 'Territory Table'[key], 'Event Table'[key]) <> BLANK(), 
    "IN", 
    "OUT"
)

This formula searches for the event key in the Territory table and returns "IN" if a match is found; otherwise, it returns "OUT". If you need a dynamic approach for filtering and conditional formatting in Power BI, you can use a measure:

In_Territory_Measure = 
IF(
    COUNTROWS(FILTER('Territory Table', 'Territory Table'[key] = SELECTEDVALUE('Event Table'[key]))) > 0, 
    "IN", 
    "OUT"
)

For conditional formatting, another measure can return numerical values instead of text:

In_Territory_Formatting = 
IF(
    COUNTROWS(FILTER('Territory Table', 'Territory Table'[key] = SELECTEDVALUE('Event Table'[key]))) > 0, 
    1, 
    0
)

After applying these formulas, the Event table will indicate "IN" for events occurring in an assigned territory and "OUT" otherwise, which can be used for filtering or conditional formatting.

Best regards,