Forum Discussion
Helper IIIHow to link two tables together?
Hi,
Any help will be greatly appreciated. I have the following table:
| Client | Service Required | Country | Status |
| Company A | VAT Registration | DE | Closed |
| Company A | VAT Registration | FR | WIP |
| Company A | VAT Transfer | BE | WIP |
| Company A | VAT Transfer | AT | Registered |
| Company B | VAT Registration | DE | WIP |
| Company B | VAT Registration | BE | WIP |
I have another table with the sales bookings and the naming convention on product name does not follow a consistent pattern.
| Client | Product Name | Sales Book Value |
| Company A | VAT Registration-DE,FR | 12000 |
| Company A | VAT Transfer-BE,FR | 1100 |
| Company B | VAT Registration-DE,BE | 50000 |
My job is to provide a status on these bookings using the first table on a per registration level. How would you best go about this?
In the second table it seems like you need to split your second column on the dash. Then you are going to need to do something to get the resulting second column from that split into rows, something like an unpivot or pivot. ImkeF should be able to help.
4 Replies
- Greg_Deckler
Community Champion
In the second table it seems like you need to split your second column on the dash. Then you are going to need to do something to get the resulting second column from that split into rows, something like an unpivot or pivot. ImkeF should be able to help.
Create a table with single unique values, probably with distinct Client names.
Then, create a link between them with cardinality one to many for both tables. Use this variable as the filter or variable in the matrix, and it will work for both tables.
PBI works better doing Star Structure for Databases. Look it up and understand the logic.
You can also watch videos from this page: https://www.youtube.com/watch?v=vjBprojOCzU (this video should help you)
v-piga-msftResident Rockstar
Hi united2win ,
If it is convenient, could you share your desired output so that we could understand your logic better?
Best Regards,
Cherry
united2winHi all,
I'm using the unpivot function as per Greg's suggestion and it's working well. Now, I have encountered another issue where I have three coloumns and I need to combine the data into one coloumn for only countries. Any suggestions? I've attached a screenshot below.
