Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Lori001
Frequent Visitor

Lookup for match and unmatch in a complex structured column

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-IDHomeNumber
1H1
2H2
3H3
4H4
5H5
6H6
7H7
1H1
2H2
3H3
4H4
5H5
6H6
7H7

 

Tabel 2

Table-Rooms  
Rooms-IDRoomNumberRoomType
2R2Bathroom
7R1Bedroom
4R4Kitchen
5R5LivingRoom
1R1Bedroom
6R1Bedroom
3R3Garage
1STAR-R1Star1
2Blank 
3STAR-R3Star3
4STAR-R2Star2
5STAR-R3Star3
6Blank 
7STAR-R2Star2

 

 

Tabel 3 - This is what I would like to get

Tabel-Star    
Home-IDHomeNumberRooms-IDStarNumberStar
1H11STAR-R1Star1
2H22Blank 
3H33STAR-R3Star3
4H44STAR-R2Star2
5H55STAR-R3Star3
6H66Blank 
7H77STAR-R2Star2

 

 

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.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.