Check column from another table if it contains two values, and return a different string as result

[Elzzie]

Hi community,

I'm quite new to powerbi, and have a specific usecase I cannot figure out. I hope you can help me out here. 

I have the following two tables:

Event

EventID	EventName
1	Birthday
2	Wedding
3	Party

Guest

Guest	EventID	GuestLocation
1	1	Outside
2	1	Outside
3	2	Inside
4	2	Inside
5	3	Inside
6	3	Outside

The two tables are related through EventID. What I want as a result is to have an extra column in table "Event", which checks the location of the guests that are present at a specific EventID. Since for EventID 3, there are 2 different locations, I want to return that as "Both". I'm searching for the following result: 

EventID	EventName	GuestLocation
1	Birthday	Outside
2	Wedding	Inside
3	Party	Both

In my current situation I have two possible locations, but in the future this might be 3 or 4. So if that could be taken into account, that would be really nice. If that is really difficult I'm happy with a solution for two locations. 

I'm looking for a solution in DAX.

[Elzzie]

@Greg_Deckler,
Thank you so much for this, my projectmanager will be very happy on Monday. 

It is not giving back the strings I would like, but I fixed that with another column matching the different options to the corresponding string I need. 

Enjoy your weekend!

[Greg_Deckler · Accepted solution]

@Elzzie PBIX is attached below signature.

GuestLocation = 
    VAR __Table = SUMMARIZE( RELATEDTABLE(Guest), [GuestLocation] )
    VAR __Rows = COUNTROWS( __Table )
    VAR __Result = 
        IF( 
            __Rows = 1,
            MAXX( __Table, [GuestLocation] ),
            "Multiple (" & __Rows & ")"
        )
RETURN
    __Result