Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.