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