Lookup for match and unmatch in a complex structured column

[Lori001]

Dear all,

I am new to Power BI and have found myself trying to solve this complex table column.

User case:

The end user wants to see all the homes with a Star number and the homes which yet don't have a Star number attached.

Explanation:

Will try to visualize it as much as I can with a sample from excel.

I have two tables (Table-Home en Table-Room) and want to merge them so that I can get all the Star information via a filter.

Every Home has a unique ID.

Every Type of Room has a unique-ID.

Every type of Star has a unique ID.

The problem is for the Star information, the application does not have a location to have this information added.

As a result, the Star information is added to the RoomType column.

I need to extract for each HomeNumber (from Table-Home) its Star which needs to be looked up in Table-Rooms --> column RoomNumber.

After that has been done, I have to be able to see the blank RoomNumbers.

Basically finding out which Homes don't have Star-Type (blank) because the STAR-number was never attached (via column RoomNumber) to HomeNumber.

Tabel 1

Tabel-Home
Home-ID	HomeNumber
1	H1
2	H2
3	H3
4	H4
5	H5
6	H6
7	H7
1	H1
2	H2
3	H3
4	H4
5	H5
6	H6
7	H7

Tabel 2

Table-Rooms
Rooms-ID	RoomNumber	RoomType
2	R2	Bathroom
7	R1	Bedroom
4	R4	Kitchen
5	R5	LivingRoom
1	R1	Bedroom
6	R1	Bedroom
3	R3	Garage
1	STAR-R1	Star1
2	Blank
3	STAR-R3	Star3
4	STAR-R2	Star2
5	STAR-R3	Star3
6	Blank
7	STAR-R2	Star2

Tabel 3 - This is what I would like to get

Tabel-Star
Home-ID	HomeNumber	Rooms-ID	StarNumber	Star
1	H1	1	STAR-R1	Star1
2	H2	2	Blank
3	H3	3	STAR-R3	Star3
4	H4	4	STAR-R2	Star2
5	H5	5	STAR-R3	Star3
6	H6	6	Blank
7	H7	7	STAR-R2	Star2

What query or dax-code would be your advise. I have spent almost all day trying different ways to solve this and I don't seem to get there 🙂

Thank you so much for your help.

[Greg_Deckler · Accepted solution]

@Lori001 I did this with 3 very similar measures. PBIX is attached below signature:

Star Rooms-ID = 
    VAR __HID = MAX('Table-Home'[Home-ID])
    VAR __Result = MAXX(FILTER('Table-Rooms',[Rooms-ID] = __HID && LEFT([RoomNumber],1) <> "R"),[Rooms-ID])
RETURN
    __Result


Star Number = 
    VAR __HID = MAX('Table-Home'[Home-ID])
    VAR __Result = MAXX(FILTER('Table-Rooms',[Rooms-ID] = __HID && LEFT([RoomNumber],1) <> "R"),[RoomNumber])
RETURN
    __Result



Star = 
    VAR __HID = MAX('Table-Home'[Home-ID])
    VAR __Result = MAXX(FILTER('Table-Rooms',[Rooms-ID] = __HID && LEFT([RoomNumber],1) <> "R"),[RoomType])
RETURN
    __Result

[Lori001]

Thank you so much @Greg_Deckler! And for including the Pbix. This was really helpful.