Forum Discussion
Helper IIConnecting Two Tables to form one matrix table
I have two tables.
One showing the family -> product ->colour level with revenue and cost details
The other table is the investment cost with details at family level.
I tried creating an investment id = family & year to form linkages but it is not working in powerbi.
The investment row is adding all the numbers up and not reacting to the family level slicer.
What I hope to achieve:
Here are the links to my excel and powerbi files.
https://drive.google.com/drive/folders/1T8L_o5lbAXIa-Hkh6eDDx0Iejb4i_yYT?usp=sharing
Thank you so much.
Following modeling best practices, you need to change the structure of the model to include Dimension tables for fields common to both tables as follows:
You then use the fields from the Dimension Tables in the visuals, measures, slicers, filters... These dimension tables filter the equivalent rows in both tables.
The slicer is from the Dim Family table; the year field is from the Dim Year table. You will get:
I've attached the sample PBIX file with the changes
9 Replies
- VahidDM
Super User
Try to add an Investment column to your first table "family -> product ->colour" by using the lookup DAX code, then use all columns from one table to create your Matrix/Table.
Table 1 = family -> product ->colourTable 2 = the investment cost with details at the family level
Try this code to add acolumn to the Table 1:
Investment = LOOKUPVALUE('Table 2'[Total Investment],'Table 2'[Investment ID],'Table 1'[Investment ID])
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your Kudos ✌️!!
milkywaypowerbiHi,
Thank you but the solution is not working. Another issue is that the investment table has 2019 while the revenue table only starts from 2020.
The investement family level is for the whole family and should not be assigned to product and colour level.
PaulDBrownCommunity Champion
Following modeling best practices, you need to change the structure of the model to include Dimension tables for fields common to both tables as follows:
You then use the fields from the Dimension Tables in the visuals, measures, slicers, filters... These dimension tables filter the equivalent rows in both tables.
The slicer is from the Dim Family table; the year field is from the Dim Year table. You will get:
I've attached the sample PBIX file with the changes
