Forum Discussion
Check column from another table if it contains two values, and return a different string as result
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 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
2 Replies
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
Community Champion
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
