cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

@ 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!:
Mastering Power BI 2nd Edition

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

@ 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!:
Mastering Power BI 2nd Edition

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors