Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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 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.