Forum Discussion
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-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.
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
2 Replies
- Greg_Deckler
Community Champion
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 __ResultThank you so much Greg_Deckler! And for including the Pbix. This was really helpful.
